Have seen a couple of posts similar to my scenario, but nothing that actually resolves my issue.
- 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
log_reuse_wait_descis reporting "LOGBACKUP" in
dbcc opentranis 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)
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!