transaction log too big 1

[lelo25]

Hello,
We are doing a bulk insert (million records) into the database .Suddenly we noticed that the transaction log grow
enormously and arrive to a size of 16 GB.
What can i do to minimize the size of the transaction log
and let tke bulk insert run without logging .
RGDS.

 

[gradley]

You may want to try breaking up the bulk insert into smaller parts, possibly performing concurrent inserts of these smaller parts. If you have your database recovery model set to "SIMPLE", it will truncate the log after each bulk insert. Unfortunately the logging needs to occur for rollback purposes.

Another option I've used in the past is to create a second log file on a different drive. The first log will still be populated first so you will need to set a max size allowing the second file to start logging.

 

[lelo25]

hello,
thanks for yr reply
How can i set database recovery model to "SIMPLE",
RGDS.

 

[gradley]

Right click on the database in Enterprise Manager and select properties. Click the "Options" tab and change the recovery model to "Simple" from the drop-down list.

Please be aware that if you want to go back to "Full" or "Bulk-logged" recovery, you should do a full database backup right away since the "Simple" model truncates any logging after a transaction is complete.

Good luck.