How to shrink a Microsoft SQL transaction log file

The transaction log file contains a record of every transaction against the database, since the last transaction log backup. All changes in the database are first saved to the transaction log, before being updated in the main data file.
The transaction log file can allow a database administrator to recover a database back to a specific point in time, rather than simply back to the last full backup. If you find there was some sort of database corruption at 4:30pm, you could recover the database back to 4:29pm using the transaction log file.
Because of these abilities, the transaction log file can actually grow over time, and become excessivly big, and potentially filling up the hard disk space on the machine that Microsoft SQL is running on.
Shrinking the transaction log file required two parts. First of all you need to truncate the log file itself, and then secondly you need to shrink the file itself. The truncate part clears the contents of the file, but it doesn’t actually reduce the file size of the transaction log on the file system.
To truncate/clear out the transaction log, you need to perform a transaction log backup. If for some reason you are not able to do a transaction log backup (usually due to the size/disk space restrictions), you can perform a “TRUNCATE_ONLY” backup, which just clears/truncates the transaction log without creating a backup file.
To do a “truncate only” backup, you would run the following query:

BACKUP LOG [<database_name>] WITH TRUNCATE_ONLY

(Unfortunatly, since Microsoft SQL Server 2008, the TRUNCATE_ONLY options has been deprecated. Instead you need to change the database recovery model to “Simple”, and then set it back to “Full”. This has the same effect as a “TRUNCATE_ONLY” transaction log backup. For more details, check the “‘TRUNCATE_ONLY’ is not a recognized BACKUP option in SQL 2008” article).
Once that has been done, you can actually shrink the transaction log file, which will reclaim the free space from within the file itself, and let the filesystem use it again. This will typically shrink it back down to about 1MB in size.
Run the following script to shrink the transaction log file:

DBCC SHRINKFILE(<log_name>)

Note: you can run “SELECT * FROM SysFiles” to determine what the log name should be. This will be in the “name” column. It has typically got a _LOG tag appended to the name.
Or, for a more automated approach, this will automatically get the log file details, and shrink it (Make sure you have truncated the log file first).

DECLARE @LogFile varchar(256)
SELECT @LogFile=name from Sysfiles WHERE UPPER(RIGHT(filename, 4)) = '.LDF'
DBCC SHRINKFILE(@LogFile)

For more information on what the transaction log file is, please read the “What is the SQL transaction log file?” article.