2

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.

0

3 Answers 3

2

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

0

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

6
  • Hi, the VLFs: i had 514 in total, the newer ones where status 0, approx 350 of them where status 2, being created in batches of 4 or 5, they where situated from row 3 downwards.
    – Lee Hill
    Sep 17, 2018 at 16:16
  • So do these get changed from 2 to 0 when you perform a log backup (except for the head one/current)? Sep 17, 2018 at 17:52
  • unfortunately not
    – Lee Hill
    Sep 18, 2018 at 7:53
  • Anything else going on her? Replication? Active transaction? Database mirroring? Those should be reported with different log_reuse_waitdesc, but you never know... Sep 18, 2018 at 8:06
  • Replication was tested a long long time ago, but is not currently running, no active transactions and no Database mirroring.
    – Lee Hill
    Sep 18, 2018 at 8:10
0

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

6
  • :D jadarnel, you will be happy to hear that finally the Board of Directors have accepted that we need to migrate to a new ERP and therefore different platform! Anyway that's a whole new set of discussions. 1. throughput could be low, nothing is done after the Index rebuild, therefore i could manually call a checkpoint after the operation to force a clear down of the VLFs. 2. what defines the size of a VLF? is it something that is set or is it adjusted by the database? what are the implications of smaller VLF's? i assume CPU overhead and Disc I/O to extend the file in smaller increments?
    – Lee Hill
    Sep 18, 2018 at 8:05
  • unfortunately, i had to "fix" it by breaking my LSN chain and rebuilding it, but i will try to "break" it in a controlled manner tonight and post the output of DBCC loginfo
    – Lee Hill
    Sep 18, 2018 at 8:06
  • @LeeHill Just to clarify, the CHECKPOINT won't clear the VLFs, but it should allow the log backup to clear them normally. The size of VLFs is determined by your initial log file size (when the file / database was created) and your log file autogrowth settings. You can read about this in detail here: Transaction Log Physical Architecture Sep 18, 2018 at 12:54
  • CHECKPOINT ye, that is understood. i will read up!
    – Lee Hill
    Sep 18, 2018 at 13:43
  • @Lee Cool, just making sure about checkpoint =) By the way, I updated my answer with the low-effort possible solution of simply not rebuilding this index. Also, congrats on the upcoming move off of your ancient ERP - which I assume is Dynamics since I have clients stuck on 2005 for that reason haha. Sep 18, 2018 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.