Why can’t I shrink the transaction log after a log backup

I run into this from time to time and I figured I’d share my experience since I don’t see it many other places.  Let’s say you have a database, pretty high transaction, and for whatever reason, you need to shrink the transaction logs.  I’m not going to give you the spiel about how you shouldn’t do this in production, truth is, sometimes you need to.  You click through the GUI, tell it where to backup the transaction log, sit 10 minutes and when it completes you try to shrink it.  And nothing.

If this database is in an AlwaysOn High Availability group, you probably went to the AlwaysOn dashboard to check synchronization status.  Yep, all synchronized.  At this point, you spend 10-15 minutes wondering what’s going on, so you decide to try to backup the transaction log again, this time you only have to wait a minutes, try the shrink and the same thing happens.

What’s going on is that data is being written to the transaction log during your backup.  In this scenario, what I do is script out several backups and shrinks of the transaction log and run it at once.  You might need to run this a few times, but eventually you will be able to get the transaction log shrunk.  Note, that I used NUL backups here, definitely not recommended for production unless you remember to take a full backup afterwards and are willing to give up point-in-time recovery since the last successful t-log backup.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: