May 7, 2008 at 5:00 am
I am running a job in which step 1 is a dts task. The task fails as the log file of a database becoming full.
I have truncated the log using 'Backup log db_name with no_log' and also shrinked the log file alone.
When I check through All tasks --> Shrink files -> Log file is showing free space as 99%, but still when i run the dts package it is failing because of the log space.
Anybody please let me know what is the reason and will resolve this issue.
May 7, 2008 at 6:47 am
Whatever the DTS package is doing, the transaction is so large that it's filling the log file. Break the transaction down into more discrete parts so that you get a series of commits. That or get some more disk space.
"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 9, 2008 at 3:52 am
Thanks for the reply. I have increased the disk space but i continuing to get the error. After increasing the space sometimes it works and again getting the error.
May 9, 2008 at 5:10 am
You should look into breaking down your process into smaller steps. Insteading of processing one million rows (or whatever it is) as a single transaction, run them hundred thousand row batches, commiting the transaction at the end of each batch.
"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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply