VLF remains active and unable to truncate log after full and transaction log backup

Thomas 2022 1 Reputation point
2022-06-18T07:57:58.157+00:00

I have a DB in full recovery mode on SQL Server 2016.
Until recently, I have been able to perform transaction log backup and the used space in the log file would be recovered and could be reused by new transactions.
Right now, while I run regular full backup and transaction log backup, the used space is not recovered.
I noticed that, even right after a tran log backup, the number of active VLF does not change and remains equal to the total VLF count. There are 0 inactive VLF.
Subsequent tran log backups are tiny, which tells me that the transactions are committed to the DB.
Why is the used space in the tran log not released and why do active VLF remain equal to total VLF?
Can the amount of available space in the database contribute to this condition?

I have been searching around, but not able to find how to tackle this condition.

Developer technologies | Transact-SQL
SQL Server | Other
0 comments No comments
{count} votes

7 answers

Sort by: Most helpful
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2022-06-18T08:30:34.687+00:00

    If I would make a guess out of the blue, there is a transaction that has been left open and uncommitted. The transaction cannot be truncated past the oldest active transaction. You can check for this condition by running DBCC OPENTRAN in the database in question.

    But there are other possible reasons. The more general solution is to run

       SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'YourDB'  
    

    and then take it from there.

    0 comments No comments

  2. Thomas 2022 1 Reputation point
    2022-06-18T09:03:52.637+00:00

    Hi Erland

    Thanks alot for the suggestion.
    I did run DBCC OPENTRAN earlier, which returned nothing.
    I'll believe the result of log_reuse_wait_desc was NOTHING, but I will need to check this again tomorrow when I can access the system again.
    Is there anything else which can cause this condition?

    Underlying HW has no issues and I see no errors anywhere. Backups succeed every time.
    I plan to restore the DB to a staging server and see if I can reproduce the issue there.

    Could any DB settings, besides what you already mentioned, cause this condition?
    I can mention that we ran this DB with an in-memory OLTP a few weeks ago, but this was removed.


  3. Thomas 2022 1 Reputation point
    2022-06-19T06:57:48.863+00:00

    Result of "SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'YourDB'" showed LOG_BACKUP initially.
    So I took a log backup which changed this to NOTHING.
    But Active VLF is still equal to VLF Count and there are no Inactive VLF.
    The used space in the log remains unchanged.

    DBCC OPENTRAN show no transactions.

    I am a bit lost on how to possibly resolve this issue.

    Can I somehow create a new log file and drop/delete the current tran log?


  4. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2022-06-19T10:45:51.22+00:00

    So there is a way to throw the current transaction log away, but it is not a step to take lightly. This will obviously break the log chain, so before this you do this, you should run DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS to make as sure as possible that you don't have any corruption. I would also recommend that you run this on a restored backup to be more certain.

    The way you do it is that you create a database snapshot. You can do some operations in the source database, but nothing you want to keep. To wit, next step is to revert from the snapshot. You do this with RESTORE DATABASE, saying FROM DATABASE_SNAPSHOT rather than FROM DISK. When you restore the snapshot, the log will be thrown away, and you will get a transaction log of half a megabyte. Which you obviously you should grow to the desired size immediately.

    I would strongly recommend that you first try this operation in your dev environment.


  5. Seeya Xi-MSFT 16,586 Reputation points
    2022-06-20T09:25:05.927+00:00

    Hi @Thomas 2022 ,

    Welcome to Microsoft Q&A!
    In addition, you need to set up the automatic growth. You can set it to grow by a fixed size. Depending on your situation, you can set it to a larger value. Try a few more log backups to see if the state of the VLF changes.
    If more inactive VLFs are found after multiple backups, you can shrink it after the backup.

    Best regards,
    Seeya


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.