Shrinking an MS SQL Server Transaction Log

| October 6, 2010

Note: While the information below specifically pertains to SQL Server 2005, the concept is the same for both version 2000 and 2008.

Shinking an MS SQL Server 2005 transaction log may be necessary if the log file grows too large due to missed backups or an extended period of high database activity. The transaction log backup will clear the log out but its size remains until the administrator manually performs a shrink operation.

Shrinking the log file(s) can be done either through the Enterprise Manager GUI or at the Transact-SQL command prompt. In either case you will need to perform a transaction log backup as the first step. Log files can only be shrunk if they’re not completely full. You may already have a process to backup the transaction log. One way is to back it up to disk with something like:

BACKUP LOG databaseName TO DISK = 'backupFileName'

Once the log has been backed up you should be able to perform the shrink operation. This can be performed at the Transact-SQL prompt with the following command:

DBCC SHRINKFILE (fileName, targetSize) WITH NO_INFOMSGS

As I previously mentioned, you can also shrink the log via the Enterprise Manager GUI. Simply right click the database, select “Tasks”, then “Shrink Files”.

At times you may find that running through the above steps does not shrink the log file. First, I would try running a second backup of the same transaction log. This should clear things out and allow the log file to be shrunk. If not, you may need to make sure the log file is being cleared after the backup. Remember, a full log will not shrink!