May 6, 2010 at 7:50 am
Hi everyone,
I have to work with a very large set of data and this causes my transaction log to fill up (68GB of space). I was wondering what are the options that I have with SQL Server 2005 Enterprise when it comes to large transactions? Also, what are some of the ways that I can load data in smaller chunks? Thanks in advance.
Almost forgot, the database is in simple recovery mode.
May 6, 2010 at 9:28 am
Depeding on what type of transaction you can use 'bulk logging' or use SSIS to do your inserts (bulk).
You cannot disable logging completely in SQL.
You may want to post your code here - it may be generating un necessary transactions.
May 6, 2010 at 9:35 am
Can you break up the transaction? There are times when you can "manually" handle the transaction. Break it up into chunks, and then undo it if one of the chunks fails. Or retry it.
More information on this would help. What does the transaction consist of?
May 6, 2010 at 11:57 am
I'm with Steve. The only way I know to make transactions smaller is to... make the transactions smaller. Break apart the data in some fashion so that you're moving it in smaller chunks.
Another thing is to look at the operations you're doing. Can they be done in a better way. Do you have a cursor that inserts 1000 records in 1000 calls? Maybe substituting INSERT...SELECT, etc. Basically, look at the operations that are being logged and determine if you can reduce, change or eliminate them in order to make the transaction smaller.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 6, 2010 at 2:37 pm
Thanks all, I think that breaking down the transaction to 5 million at a time will be the route I'll go. If I run into problems, I'll report here, but otherwise, everything should be fine. Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply