Nikolay Pavlov

In Microsoft SQL Server sometimes you can observe the LOG file growing uncontrollably and unexpectedly to a size times the size of the DATA file.  I’ve witnessed  a database with data file of 200 MB and a log file of 4 GB.  Not only harddisk space is wasted, but the backup file becomes too inconvenient to drag around even heavily compressed.

Why does the LOG file grow that much is a different story, which I am not prepared to tell now.  The question is how to make the LOG file small again within the “normal” boundaries.  Using the “Shrink database” command does no good.  Backup and restore of the database gives you the same old large log file.

The only solution I know so far involves the following steps:

1. Create a new, empty database.
2. Script all the db structure, and use it to create an identical copy of your old one into the new database you just created.
3. Use bulk copy to transfer all data from the original database to the new one.

Voila, the LOG file is back to normal.

Note that you might need to disable certain triggers and foreign keys before the copy operations - that depends completely on your database.

This entry was posted on Tuesday, July 7th, 2009 at 1:40 pm and is filed under Database & SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.