Shrinking your SQL Server 2005 Transaction Log

I get this question all of the time, and there is a great support article from Microsoft on how to do this.

One of the ways to truncate the log file is to simply detach the database and re-attach it without the log.

I DO NOT recommend that unless it is a development db that you don’t care about.

The proper way to do this is to first backup the TRANSACTION LOG portion of the database by using the following command:

BACKUP LOG <DatabaseName> TO DISK = '<BackupFile>'

And then running a shrinkfile command:

DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS

If you run the shrinkfile command without first doing a log backup, all it will remove is the empty space in the ldf file, no matter what target size you specify.

Here is the Microsoft support article that explains this.

More later - joel