Shrinking SQL Server Log Files

For the new year I’ve decided to keep our testing environment more up to date than it has been in the past by restoring the databases from our backup server at the first of the month.  One of the problems I’ve run into though is I run out of space on the log drive.  I’ve read the articles that say you should never shrink the log but this is a test environment, so I think I’m ok.  So to shrink the logs down I do the usual:

  • In SSMS right click the DB – go to Tasks->Shrink->Files
  • Change the file type to “Log”, make sure “Release unused space” is selected and click “OK”

So that’s worked just fine in the past – it didn’t work this time though.  The shrink file dialog would say the log file could be shrunk anywhere between 89% and 99%, but would only shrink about 10% when the process ran.  So I ran across this post:

http://rusanu.com/2012/07/27/how-to-shrink-the-sql-server-log/

From that I learned that where the head and tail pointers in the log are affects how the unused space can be released.  I figured this was my problem, so now how to fix it…  It turns out there is a handy way of telling why the log file won’t shrink – you can use a sql query:

select log_reuse_wait_desc from sys.databases where name = 'DBName' 

And then using the table on this page:

http://msdn.microsoft.com/en-us/library/ms345414.aspx

Something interesting I noticed was the log_reuse_wait_desc value didn’t populate right away in the sys.databases table.  For one of my databases I did a log shrink (which didn’t shrink the log that much) and when I looked at the log_reuse_wait_desc value it was “NOTHING”.  I then did another log shrink (which did nothing), but when I checked the log_reuse_wait_desc it had changed to “LOG_BACKUP”.

So my steps were:

  • Shrink the log file two times in SSMS
  • If the file didn’t shrink:
    • Check the log_reuse_wait_desc – it was always “LOG_BACKUP”
    • Backup the log by:
      • right clicking the db – choose Tasks->Back Up
      • change “Backup Type” to “Transaction Log”
      • choose a temp file as the destination to backup to and hit OK
    • Shrink the DB log again – this time it should shrink the specified amount
    • Delete the backup file