Hi,

I have 5 DTSsses that are executed by a Main DTS. All packages insert
lots of data into our DB. I've noticed that our Server's transaction
log reached its limit of 100 GB. We cleaned the log file and now we
are back in business. What is the best practice when inserting lots of
data into the db? What to do about the Transaction Log file fenomenal
growth ratio??? How to commit these transactions before executing the
next DTS?

I've read about doing batch inserts of 1000 rows, and checkpoint...
How to do checkpoint?

Thanks in advance!

Couple things:

You might want to drop any existing indexes before you
insert the data, if you are deleting an entire table, use
truncate instead of delete (minimal-logged), think about
using BCP, bulk insert or select into (if possible, they
are minimally logged as well)

You also might want to run in batches and truncate your
log in between batches (again, depends on when and on what
your inserts occur on)

HTH

Ray Higdon MCSE, MCDBA, CCNA

Thanks for the follow up,

Im gonna run it in batches, i cannot use Bulk Copy. How do I truncate
the log in between batches?

thanks in advance!


"Ray Higdon" <rayhigdon (AT) higdonconsulting (DOT) com> wrote

Are you using FULL recovery model? If so then you don't want to truncate
the log, you should issue a log backup. If your not intending to do log
backups then set the recovery mode to Simple and you shouldn't have to worry
about it. I suspect that you have it in Full and are not doing regular log
backups and that is why the log has grown so large.

--

Andrew J. Kelly
SQL Server MVP


"Joca" <jjone99 (AT) hotmail (DOT) com> wrote

Yes, at first the recovery model was set to Bulk-logged. We've set it
to simple now. We don't really need to log any transaction while the
DTSs run.

Did you say that I don't need to type CHECKPOINT after I execute each
DTS? Also, not sure if this has happened to anyone else, is it
possible that if the transaction log is full then stored procedures w/
cursors and temp tables will not run correctly??

Thank you in advance for all the shared knowledge!