Why won’t my log file shrink?

While it is best practice to never shrink a database or individual file in SQL Server, there are times that it has to be done. Whether you have a test database that sits on an undersized test server or a production database that had a once in a blue moon transaction go though that bloated the log file, you may find yourself needing to recover the space.

If you need to shrink the log file, first you need to find the logical file name. You can do this by connecting to the bloated database and running the following:

Select Name, Type_Desc

  From sys.database_files

 Where Type_Desc = 'Log'

Then take that name and run the following DBCC command:

dbcc shrinkfile('<logical_logfile_name>',10)

The 10 signifies leaving 10% free space. If you want to recover the maximum space, use TRUNCATEONLY in place of 10.

But sometimes when you use this command nothing happens, why? In the messages when I ran the DBCC command, I see the following:

Cannot shrink log file 2 (<logical_logfile_name>) because all logical log files are in use.

Hmm, there’s plenty of space in the log I should be able to recover but cant. Why? Let’s look at the log_reuse_wait_desc column in sys.databases:

SELECT  Name,Recovery_Model_Desc, Log_Reuse_Wait_Desc

FROM    Sys.databases

Where   Name = <'db_name'>

On FULL or BULK_LOGGED databases you may see LOG_BACKUP, while on SIMPLE recovery model databases you may see CHECKPOINT. If the recovery model is simple, issue a checkpoint (which writes all the dirty pages in the buffer cache to disk) and then try the shrink again.

If the recovery model is FULL or BULK_LOGGED, we need to issue a backup log command to free up part of the active log: You can do that by issuing the following command:

backup log <db_name> to disk = '<file_name>'

Books Online states that it may take 2 log backups to actually free the space.

After this, you should be able to use DBCC SHRINKFILE to shrink the log file.

NOTE: IF YOU ARE USING THE FULL OR BULK_LOGGED RECOVERY MODEL YOU SHOULD BE TAKING LOG BACKUPS ON A SCHEDULED BASIS. NOT ONLY WILL THIS KEEP YOUR LOG FROM GROWING UNCONTROLLABLY, BUT IT WILL ALSO SAVE YOUR BACON IF YOU EVER HAVE TO RESTORE THE DATABASE FROM A BACKUP, WHICH IS THE MOST IMPORTANT REASON.