Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DB will become read only. We cannot make any modifications here, How to avoid this scenario Expand / Collapse
Author
Message
Posted Wednesday, February 26, 2014 1:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 9:43 AM
Points: 1, Visits: 27
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
Post #1545577
Posted Wednesday, February 26, 2014 2:04 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 13,085, Visits: 12,551
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1545580
Posted Wednesday, February 26, 2014 2:20 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 863, Visits: 7,350
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
Post #1545586
Posted Wednesday, February 26, 2014 2:35 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 7:59 PM
Points: 1,194, Visits: 2,222
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

Post #1545593
Posted Wednesday, February 26, 2014 8:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1545679
Posted Wednesday, February 26, 2014 8:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1545680
Posted Thursday, February 27, 2014 9:07 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 7:59 PM
Points: 1,194, Visits: 2,222
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
Post #1545953
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse