How to shrink the transaction log

By: Brady Upton

One thing that I see a lot of administrators ask about is transaction log size and how to truncate it. Log records that are not managed correctly will eventually fill up the disk causing no more modifications to the database. Transaction log growth can occur for a few different reasons. Long running transactions, incorrect recovery model configuration and lack of log backups can grow the log.

Log truncation frees up space in the log file so the transaction log can reuse it. Unless there is some kind of unexpected delay, log truncation will occur automatically after a checkpoint (if the database is in SIMPLE recovery model) or after a log backup (if the database is in FULL or BULK-LOGGED recovery model). MSSQLTips.com offers plenty of tips regarding transaction log truncation, but I’ll show you two ways to shrink the log.

Shrink the log in SQL Server Management Studio

To shrink the log in SSMS, right click the database, choose Tasks, Shrink, Files:

On the Shrink File window, change the File Type to Log. You can also choose to either release unused space, reorganize pages before releasing unused space, or empty file by migrating the data to other files in the same filegroup:

Shrink the log using TSQL

If the database is in the SIMPLE recovery model you can use the following statement to shrink the log file:

        DBCC SHRINKFILE (AdventureWorks2012_log, 1)
        

Replace AdventureWorks2012_log with the logical name of the log file you need shrunk and change 1 to the number of MB you want the log file shrunk to.

If the database is in FULL recovery model you could set it to SIMPLE, run DBCC SHRINKFILE, and set back to FULL if you don’t care about losing the data in the log.

        ALTER DATABASE AdventureWorks2012
        SET RECOVERY SIMPLE
        GO
        DBCC SHRINKFILE (AdventureWorks2012_log, 1)
        GO
        ALTER DATABASE AdventureWorks2012
        SET RECOVERY FULL
        

**You can find the logical name of the log file by using the following query:

        SELECT name FROM sys.master_files WHERE type_desc = 'LOG'
        

Another option to shrink the log using the FULL recovery model is to backup the log for your database using the BACKUP LOG statement and then issue the SHRINKFILE command to shrink the transaction log:

        BACKUP LOG AdventureWorks2012 TO BackupDevice