January 16, 2008 at 8:39 am
I am running a DTS package which is loading 40 million rows using a transform data task. This is causing the log file of destination database to grows up to 40 GB and the package finally fails to as drive gets full.
Do we have an option in SQL server 2000 DTS to limit the load size after which a commit is issued or Is there is a way I can control the log file growth?
Any help on this issue is appreciated.
-RM
January 16, 2008 at 8:47 am
How you tried to use BULK insert in the DTS?
You also can change destination database recovery to avoid transaction log growth.
Regards Ramon
Regards Ramon
January 16, 2008 at 10:24 am
Hi Ramon
Thanks for the reply.
I am using "transform data task" for loading the data into Target.
I tried changing recovery mode of Destination database to simple and tried. The issue still persists
January 17, 2008 at 1:15 am
Have you tried to put destination database in BULK_LOGGED mode?
You may also consider turn off triggers while you are processing data.
Regards
Regards Ramon
January 18, 2008 at 9:13 am
Yes infact you can limit the transaction log usage with a commit on every so and so records using the data transform task in DTS. The last tab on the window when you are setting up the transform (Labeled Options) at the bottom is a text field for Insert Batch Size.. whatever number you enter there is how many records will be committed in each batch.
I do a heavy amount of warehousing of data, meaning hundreds of these sorts of transforms a night, the batch sizes saved huge amounts of transaction log space. You will want to test the batch size to find the best performance for the data.
Hope this helps!
January 18, 2008 at 9:30 am
I will try this option today. Thanks David.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply