Archive

Monthly Archives: January 2018

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.