SQL Server Transaction Log VLFs not being released (Status 2) on log shipped Database

Question

Have seen a couple of posts similar to my scenario, but nothing that actually resolves my issue.

Scenario:

• Routine index rebuild happens, once a week, 2 AM Saturday morning.
• Log file grows about 15x the usual size.
• Transaction log backup is taken (every 10 minutes, 24 x 7).
• FULL backup is taken every day at 3 AM.
• VLFs are remaining "active" (status 2) when i look at dbcc loginfo
• log_reuse_wait_desc is reporting "LOGBACKUP" in sys.databases
• dbcc opentran is reporting no active transactions
• @@version: Microsoft SQL Server 2005 - 9.00.5069.00 (X64) Aug 22 2012 18:02:46 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Problem:

So, my issue is simple, because of hardware restrictions and time parameters, i need to keep transaction log size as small as possible. i believe the reason it is not releasing the space is because the VLFs are active and it thinks it needs to take a LOGBACKUP to release, but after countless log backups, the log_reuse_wait_desc is still reporting awaiting a log backup!

I could change the recovery model to simple, shrink and change back, but this breaks my LSN chain and my Log Shipping implimentation, so its not really a viable solution!

TIA.

Answer 1

After some more investigation i found that when a large log backup was required to be taken, the time taken produce the trn backup file was elapsing a timeout period of 5 minutes set by the lanmanserver service.

The error being reported was error 64 (error not found) which is actually quite a common issue apparently as described here; so even though the log backup was being created, the SQL engine couldn't verify that the backup had been successful.

In turn, the VLF's where not being set to 0, hench seeing the issues that were described in the Question.

thank you for your help guys, but i think the root of the issue was the error being reported (i didn't mention that as i could see the backup files and logshipping was still processing them, so i didn't think it was part of the same problem!)

Answer 2

You need to investigate your VLFs. How many do you have? Of those you have, how many are in use (status = 2)? And, of those which are in use, where are they?

Don't worry too much of the "LOGBACKUP" status, you will have that as soon as you aren't in simple recovery model and you also have more than one used VLF (which is pretty normal). Some more info in here: http://karaszi.com/large-transaction-log-file

Answer 3

First off: you are on an unsupported version of SQL Server. You should strongly consider upgrading. I'm sure you are probably tired of hearing that =) But it is a real concern, and something that could potentially be relevant to this problem (in other words, it could be a bug in the version of SQL Server 2005 that you're on).

It's possible that the VLFs are not being cleared because:

1. Transaction throughput on your system is relatively low, and thus CHECKPOINTs are not occurring, and thus the VLFs cannot be cleared. You can see if this is your problem by running a manual CHECKPOINT on your database, and then seeing if the VLFs are cleared (status 0) after the next log backup. Note that this could be an I/O intensive operation:

   USE YourDatabaseName;
   GO
   
   CHECKPOINT;
   

2. Another possibility is that the VLFs are large, and thus under normal operation your log records can stay in one VLF for a long time before moving to the next one

It could also be a combination of 1 and 2 above, or something else entirely.

It would be helpful if you could post the details of DBCC LOGINFO (you have mentioned summary information in a few places already, but at this point I think the details are needed).

---

Note that one possible solution to your problem is to simply not do this INDEX REBUILD, thus not incurring the massive log growth in the first place. If the rebuild is being done because of perceived performance problems, you could consider other solutions (updating statistics for instance).