Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
itsonlyme4
Posting Yak Master
109 Posts |
 Posted - 2009-06-26 : 07:37:49
|
| MS sq Server 2000.We are doing a one - time insert into a database and the log is growing to 15GB using ALL of the free space on the drive and blowing up.The insert is being done with a third party ETL tool - reading our datamart (15 million rows) and then doing inserts on one column in select rows our sql server DB. For each insert into the sql server DB, there is a trigger that writes to another table. Will simple recovery mode help me here?? Bulk-logged?? any suggestions? |
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2009-06-26 : 10:19:16
|
| Change your recovery mode to bulk logged this will not log any large bulk inserts therefore not increasing rapidly in size |
 |
|
|
itsonlyme4
Posting Yak Master
109 Posts |
Posted - 2009-06-26 : 14:01:41
|
| Thank you! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-26 : 14:23:51
|
Also try inserting records in smaller chunks.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-06-26 : 14:26:24
|
| You lose the point in time recovery option while it is set to bulk_logged, so this is something that you need to be aware of. We would not be allowed to do that in my environments due to how critical the data is. Sounds like you need to break up your process into smaller batches.Oh and switching recovery models will not help at all if you are doing this process in one batch.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
|
|
|
Log file blowing up on large insert operation