DB will become read only. We cannot make any modifications here, How to avoid this scenario

Question

DB will become read only. We cannot make any modifications here, How to avoid this scenario

sankepalli

Newbie

Points: 5
More actions
February 26, 2014 at 1:56 pm

#284016

Hello Folks,
Suppose, there are 1 million records, if we insert all these records in the log file, it will become full & DB will become read only. We cannot make any modifications here, How to avoid this scenario?
Immediate help would be highly appreciable
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

sankepalli (2/26/2014)
Hello Folks,
Suppose, there are 1 million records, if we insert all these records in the log file, it will become full & DB will become read only. We cannot make any modifications here, How to avoid this scenario?
Immediate help would be highly appreciable
Thanks

Don't insert the rows? I assume you mean you are inserting a million rows into a table since you can't insert rows into the log file. Not quite sure what you mean that you can't make any modifications. I would think that inserting a million rows is certainly making a modification.

The only way you can ensure the log file does not fill up with no other intervention is to do anything that causes any logging. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

David Webb-CDS SSCoach Points: 17398 More actions · Answer 2

Several possible ways to prevent the log filling:

Insert the records in batches and take log backups between the batches.

Switch the DB to 'Simple' recovery mode, insert in batches, switch back to 'Full' recovery mode, take an immediate full backup (this may not be advisable if you need to maintain point in time recoverability for the DB)

Allow the log to grow, or add space to the log to cover the amount of data. Remove other files from the drive if possible to free up space.

Without more information about the situation, it's tough to give meaningful advice.

And then again, I might be wrong ...
David Webb

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 3

sankepalli (2/26/2014)
Hello Folks,
Suppose, there are 1 million records, if we insert all these records in the log file, it will become full & DB will become read only. We cannot make any modifications here, How to avoid this scenario?
Immediate help would be highly appreciable
Thanks

Do it as david mentioned. You need to break the insert into smaller chunks and do the inserts.

--

SQLBuddy

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

sankepalli (2/26/2014)
Hello Folks,
Suppose, there are 1 million records, if we insert all these records in the log file, it will become full & DB will become read only. We cannot make any modifications here, How to avoid this scenario?
Immediate help would be highly appreciable
Thanks

If this doesn't sound like an interview or homework question, I don't know what does.

So what do YOU think?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

David Webb-CDS (2/26/2014)
Several possible ways to prevent the log filling:
Insert the records in batches and take log backups between the batches.
Switch the DB to 'Simple' recovery mode, insert in batches, switch back to 'Full' recovery mode, take an immediate full backup (this may not be advisable if you need to maintain point in time recoverability for the DB)
Allow the log to grow, or add space to the log to cover the amount of data. Remove other files from the drive if possible to free up space.
Without more information about the situation, it's tough to give meaningful advice.

You don't need to take a FULL backup to reestablish the log chain after switching back to the FULL recovery mode. DIFs work just fine for that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 6

Jeff Moden (2/26/2014)
sankepalli (2/26/2014)
Hello Folks,
Suppose, there are 1 million records, if we insert all these records in the log file, it will become full & DB will become read only. We cannot make any modifications here, How to avoid this scenario?
Immediate help would be highly appreciable
Thanks
If this doesn't sound like an interview or homework question, I don't know what does.
So what do YOU think?

Yep, You are right. It sounds like an interview\homework question ..

--

SQLBuddy