November 22, 2006 at 8:11 am
each time i run a dts it says "the log file for database "db name" is full. back up the transaction log to free up some log space. is there some code that i can use to do this, or to maby truncate the transaction log?
November 22, 2006 at 8:46 am
You need to check to make sure your log is allowed to autogrow.
(Enterprise manager Properties on database, Transaction log Tab, Make sure Autogrow is checked.)
If this is okay, then make sure you have enough disk space on the drive where the log is located.
I'm hoping you know what the log is for, so otherwise, there's not really a way to manage the log size thru code while the dts is running.
November 22, 2006 at 8:55 am
Also,
check the recovery mode of related databases;
Run
BACKUP LOG myDB WITH NO_TRUNCATE
before running your DTS on related databases.
November 22, 2006 at 9:06 am
One of the big decisions that has to be made when setting up a database is: how important is the data? How important is it for me to be able to restore to a specific minute? If none of that is important, if just restoring the most recent full backup is good enough - then set your recovery mode to SIMPLE. Then the log will checkpoint and free up space. If it's important to be able to restore to the minute - set the mode to FULL and do frequent log backups.
Also, as said by others, set your AUTOGROW to 'allow' and set it to a reasonable size.
Setting the autogrow to a reasonable size if very important. If you set it to 1 MB and your transaction requires 10 MB of space, the log has to grow 10 times for one transaction. That growth might not happen fast enough for the transaction. You might have enough disk space, but if the log doesn't grow fast enough for the transaction it doesn't matter how much space is on the drive.
-SQLBill
November 22, 2006 at 11:03 am
Just to add:
1. Make sure there is no old long running transactions on the database thats causing the database logs to keep shrinking.(DBCC OPENTRAN).
2.If your recovery model is set to full make sure the xaction are being backed up and you have DBCC SHRINKDATABASE as part of your weekly routine.
3.Make sure you have enough room on the server for xaction log to grow. Check if the Xaction log has a Restricted file growth (Your xaction log will not grow beyond that and will cause your error).
Thanks
Sreejith
December 1, 2006 at 9:38 am
Since you are getting the log full message when running the DTS package, I assume the correlation that the DTS package is loading/updating something that is causing the log file to fill up.
If you are loading data into the database in which the log is filling, check the options tab on your transformation task(s) (the tasks that move data from a source to a destination). One of the options is Insert Batch Size. Set that to a non-zero number. With large loads, I typically set it to 5000. Transaction commits will take place at that interval. If your database recovery option is set to Simple, once the batch is committed, it will be cleared from the transaction log file. If your database must be set to Full, you will want to set up a transaction log backup job to run at frequent intervals during the scheduled run of this DTS package to keep the transaction log file a reasonable size.
I prefer to manage the transaction log this way rather than frequent truncates and shrinks. Ideally, it's preferable to have a fixed transaction log size with the file space allocated contiguously. That minimizes transaction log file physical fragmentation which can negatively impact performance.
Hope this helps.
Ronna Williams
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply