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 |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2007-06-05 : 16:06:08
|
| Hi,Is there a way to temporarily not write to the transaction log? We have a data migration process that transfers a large number of records from one database to another. When we do this the transaction log gets very large causing size issues (disk space etc). Is there a statement or some setting you can flip to not write to the transaction log (and then a way to turn it back)?Thanks,NicNic |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-06-05 : 16:08:31
|
| No. Even in SIMPLE recovery model, SQL Server uses the transaction log.I'd suggest adding more disk space so that you are able to maintain a large LDF file. Once you have the disk space added, I would highly recommend not shrinking it as obviously it needs this space.You could rewrite your process so that it processes data in batches that way the file is more manageable. This is what we do for mass deletes that we do each night. We loop through lots of rows, but only process about 10,000 rows each pass through the loop.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-05 : 16:21:17
|
| "Is there a way to temporarily not write to the transaction log?"One of the benefits of a database like SQL Server is that if a "transaction" fails half way through then ALL of the transaction is rolled back.I'm sure you wouldn't want approximately-half of the transaction to be imported, and you to not know which had, and which had not? !!For a bulk import you could try using Bulk Logging mode - this will reduce the amount of logging. Alternatively process it in "small" batches, as Tara describes.Kristen |
|
|
|
|
|
|
not write to transaction log?