One DBA's Ongoing Search for Clarity in the Middle of Nowhere


Yet Another Andy Writing About SQL Server

Thursday, April 3, 2014

I Know I Shouldn't Shrink My Log file, BUT...

As a managed services DBA, I frequently have to clean up after other people's mistakes. (Come to think of it, as *any* DBA you spend time cleaning up other people's mistakes...)

We all know you shouldn't shrink your log file (right?  We all know this, right?) The experts constantly preach this on their blogs, their #SQLSaturday talks, and on Twitter on #sqlhelp.

You will use #sqlhelp on Twitter....You will use #sqlhelp on Twitter..YOU WILL USE #SQLHELP ON TWITTER!

HASHTAG NotQuiteSubliminalMessage

Anyway, the message from these sources usually is:

"You shouldn't shrink your LOG file because if the system needed 300GB of LOG file for an operation last night (reindexing/data load/whatever), it will need it again the next time it does that operation, even if you only run the operation once per quarter."

This is true if you are in that situation - don't shrink your LDF file to reclaim drive space between runs of your monthly payroll process - but the experts also agree that there are times you do need to shrink the log file, such as for an "out-of-control" log or to clean up excessive VLFs.

In our world, we frequently run across the "out of control" scenario, and usually one of two things has happened:

  • A client end-user has running a bizarre mistaken query that ballooned their LDF file to 750 GB on their 10GB MDF database.
  • A client has created a new database in FULL recovery without adding it to their LOG backup strategy, and now the LDF file has grown large enough to throw a disk alarm on the LOG drive.
IMPORTANT DISCLAIMER - you always need to thoroughly research the cause of the excessive LOG growth before proceeding with a shrink - if you aren't sure what has happened, research further!

At Ntirety we have a service desk team in front of the DBA team who is the first line of response when alerts/alarms come in from our clients.  This means they are the first ones to triage the disk alarms that are the indicators of a grown LDF file.

We recently had an issue where the client's MDF file was 30GB and the LDF file had grown to 190GB, nearly filling the 195GB LOG drive.  The service desk employee who responded to the alarm investigated and then tried to shrink the LDF file.  It wouldn't shrink, so she tried taking a LOG backup (twice) and yet the LDF file still wouldn't shrink, so she escalated to me.

I went to my go-to for a LDF file that won't shrink:
SELECT name, log_reuse_wait_desc
from sys.databases
As described in this Technet article, there several possible values for the log_reuse_wait_desc field:

Under normal operating circumstances you will see either NOTHING (there is no current log_reuse_wait) or LOG_BACKUP (the LDF can not be "cleared" until a LOG backup).

When a LDF file won't shrink, the most common problems are REPLICATION, DATABASE_MIRRORING, or LOG_BACKUP.
  • REPLICATION - the log was not able to be cleared (and therefore shrunk) because it was pending sending transactions to a replication target.  Check for publications on the database (Replication>>Local Publications in Management Studio or SELECT pubid, [name], [description] from syspublications in the database in question) if you aren't aware of ongoing replication. 
    • If you find a publication (you normally will) check Replication Monitor (right-click the publication in Management Studio and select Launch Replication Monitor) to see where replication is slowed down - you may find the LogReader isn't running, for example.  Extended replication troubleshooting is beyond the scope of this post.
    • If you don't find a publication, there may have been one in the past that didn't get properly cleaned up.  In this case you may need to run sp_removedbreplication to remove artifacts of that replication.
  • DATABASE_MIRRORING - similar to REPLICATION, the log was not able to be cleared because it was pending sending transaction to a mirroring target.
    • Troubleshooting for this wait is again similar to replication - check the Database Mirroring Monitor (right-click the database, select Tasks, then Launch Database Mirroring Monitor) to see where the process has slowed down or stopped.
  • LOG_BACKUP - the log can not be cleared because it needs to be backed up
    • Troubleshooting is easiest of all - take the backup!
In this particular case, the log_reuse_wait_desc for the database was REPLICATION, and the issue was an old publication that hadn't been deleted.  Deleting the publication (and issuing a CHECKPOINT to be safe) allowed us to shrink the 190GB LDF file down to it's normal 1.5GB in a single operation!

Hope this helps!


  1. Another reason to shrink the transaction log is if you have a lot of virtual log files as a result of very small auto-growth settings. A heavily fragmented transaction log can take 15% longer to backup and recover. This may sound small, until you have a really large and active database that needs transaction log backups every few minutes.

  2. Another common reason for not being able to shrink the log is that the "current" chunk of the log file is the last chunk of the file. This is always the case when you have done something to cause the log file to grow continuously. When you backup the log file, the log is cleared except for the current chunk. When you shrink the log file, it can only shrink from the end of the file back to the current chunk and, since the current chunk is the last in the file, the shrink cannot shrink at all. So, you have to wait until enough transactions have been written to the log to fill the current chunk and the next chunk will be the first in the file (since you just backed up the log). Then, you can backup the log again which will backup the last physical chunk and then when you shrink, it actually does shrink.

  3. This is one technology that I would love to be able to use for myself. It’s definitely a cut above the rest and I can’t wait until my provider has it. Your insight was what I needed. Thanks

  4. This is one technology that I would love to be able to use for myself. It’s definitely a cut above the rest and I can’t wait until my provider has it. Your insight was what I needed. Thanks