Cannot shrink the log – now what

Happy New Year anyone (and Gong Xi Fa Cai, since it’s rapidly approapching).

Now this one really asks for a blog post, since I saw it too many times on various forums and I just got it on one of my production servers.

I just got an alert that we’re running low on space on the BIZTALK machine. So, quick and dirty check:

Uh-oh, this is not good, 99 MB for the data file and almost 10 times the size for the log file. Since I’m in FULL recovery model, this tells me that there’s no transaction log management (read transaction log backups). Sure enough, when I run:

select log_reuse_wait_desc from sys.databases
where name = ‘BAMAlertsApplication’

I get the following:

OK, I did a log backup, then I tried to shrink the log file, guess what, I’m still with the same log file size! What to do, what to do…

Well, run the dbcc loginfo

And look for the status column, for anything that spells “2” – that is the active portion of the VLF. And now for the culprit:

Sorry for the ugly print screens, it’s a quick post, I’m in the middle of troubleshooting thisJ.

Anyway, going back, the last record shows me the status of 2. And it just so happens that whenever you want to shrink your log, it will do it backwards, starting with the VLFs that are at the end. The only condition for the space to be released is that the VLFs are inactive (that is, the status is 0). In my case, I have all the conditions not to have my transaction log shrunk, since the very last VLF is marked as active, so the space cannot be given back to the operating system.

But I really need that space, what to do, what to do…

Easy enough, backup the log againJ. And now if I run the same DBCC:

Now I can try to shrink the log again. And here’s the result:

Key takeaways:

• If you are in FULL recovery model and never done a transaction log backup, you either switch to SIMPLE or start doing transaction log backups
• If you absolutely need to shrink the transaction log (as it was my case, running out of space), check it with DBCC LOGINFO (run this in the context of the database you care about) and check which ones are marked as active.
• If it just so happens that the last one is marked as active, make a transaction log backup again. And againJ. Until problem is solved – by the way, this goes for servers with very low activity, as it was my case.
• After the last VLF is marked as inactive, you’re good to shrink the log file.

I hope this helps some soul out there.

Best regards,
io