Shrinking a Sql Server Transaction Log

Here is a good post that has detailed how to shrink the Microsoft SQL Server tranasaction log using DBCC Shrinkfile. Having had to do this many times, it is nice to see it detailed so simply. For perpetuity I have copied the directions below, but full credit goes to David for posting this.

First things first BACK UP YOUR DATABASE! Seems fundamental enough but alas in haste it can be forgotten.




Launch SQL Server Management Studio(2005\2008 ) or SQL Server Enterprise Manager(2000).

Open up a query window associated with the database with the large transaction log.
SSMS: Right-click on the DB and select new query.
SSEM: Select the DB, open the Query Analyzer under Tools.

Get the logical name of the transaction log file.
SSMS: Right-click on the DB, select Properties, then in the Files screen, get the Logical Name. It usually ends in _Log but may not be the same name as the DB name…
SSEM: Right-click on the DB, select Properties. Under the Transaction Log tab get the file name of the log.

Execute the following commands, substituting LOGFILENAME_Log and DB with the logical name of the database log file and the database name respectively:

1. DBCC SHRINKFILE(LOGFILENAME_Log)
2. BACKUP LOG DB WITH TRUNCATE_ONLY
3. DBCC SHRINKFILE(LOGFILENAME_Log)

Alternatively, you can attempt to shrink it to a specific size doing the following giving the desired size in MB (don’t do 0):

1. DBCC SHRINKFILE(LOGFILENAME_Log,SIZE)
2. BACKUP LOG DB WITH TRUNCATE_ONLY
3. DBCC SHRINKFILE(LOGFILENAME_Log,SIZE)

Finally, should all else fail in reducing the size, the final last resort can be to physically delete the transaction log.

1. Take the DB offline either via detach or taking it offline.
2. Delete the transaction log
3. Bring the DB back online

Results may vary with that last method.