How To Shrink a SQL Server Database Transaction Log

This is purely a reminder for me so I can remember how to reduce the size of a huge transaction log file in a SQL Server database. I've been getting annoyed with running out of disk space, and couldn't manage to get Enterprise Manager to shrink the log files so I looked this up from various places on the Microsoft website.

Enter these commands in query analyser, enterprise manager, or whatever:

backup log mydatabasename with truncate_only;

use mydatabasename;

select * from sysfiles;

Look to see what is in the name column of the log file. Use that in the next statement.

dbcc shrinkfile (mydatabasename_log, 10);

The 10 refers to the desired size of the log file in Mb.

Hey presto, the huge log file has now become a 10Mb log file.


Delete the Transaction Log

Or you can delete the transaction log altogether using the SQL Server Enterprise Manager so long as nobody is connected to the database. Detatch the database first, delete the log file manually using Windows Explorer or whatever, then re-attach the database. A new log file with automatically be created. You can find the name of the file you need to delete by doing right-click -> properies on the database before deleting it. When trying to re-attach with a missing log file on newer versions of the management studio, I get an error complaining that the log file doesn't exist. I just remove the log file from the list of files to re-attach, and it continues and re-creates it.

As I said, this is a reminder for me, so if you try this please test it first, and don't blame me if you lose your data.