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

log-shippingsql serversql-server-2005transaction-log

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.

Best Answer

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!)

Related Question