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:

• NOTHING 
• CHECKPOINT 
• LOG_BACKUP 
• ACTIVE_BACKUP_OR_RESTORE 
• ACTIVE_TRANSACTION 
• DATABASE_MIRRORING 
• REPLICATION 
• DATABASE_SNAPSHOT_CREATION 
• LOG_SCAN 
• OTHER_TRANSIENT

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!