0

I have a SQL 2017 Enterprise server where more and more VLFs are being consumed and none are being released. Log backups are running every 15 minutes. It is in an AG but this is working correctly (readable secondary is up to date). log_reuse_wait_desc is sys.databases says 'NOTHING'. There are a bunch of open transactions, but according to sys.dm_tran_database_transactions, none are using any bytes of transaction log. Having a bunch of open transactions is pretty normal for this server. The application has always done this (many years) but this is the first time we've experienced this issue. sys.dm_db_log_info() shows I have 343 VLFs with status of 2 - 19 of these have been like this for 2 days now.

Thoughts? My working theory is that some kind of cleanup thread in SQL had died and that bouncing it will probably get things going again but that requires restarting the application which is a nightmare.

EDIT: Despite the bulk of the VLFs being marked as in use (including the ones at the end of the log), I was able to shrink the log file and regrow it. I have bad feeling that this will just have cleaned it up for now though.

Further edit... A colleague sent me https://www.sqlskills.com/blogs/paul/lazy-log-truncation-or-why-vlfs-might-stay-at-status-2-after-log-clearing/ The article linked from that indicated that dbcc sqlperf(logspace) should show the real amount used. This is continuing to go up, ever after log backups.

Improve this question
2
  • What do you mean by “ more VLFs are being consumed and none are being released”?
    – David Browne - Microsoft
    Commented May 18 at 22:17
  • There are 447 VLFs in the log. Two days back 19 had vlf_status of 2. Now 343 of them have status 2. This includes the same 19 as before (I captured the vlf_begin_offset)
    – Bruce Benson
    Commented May 18 at 22:35

1 Answer 1

Reset to default
1

What I didn't know is that for databases in AGs, VLFs can remain with a status of 2 even after they are freed up by a backup. https://www.sqlskills.com/blogs/paul/lazy-log-truncation-or-why-vlfs-might-stay-at-status-2-after-log-clearing/?unapproved=1206815&moderation-hash=b2c1b6d2576c7b4085d88875ee12ac48#comment-1206815

and

https://blogs.msdn.microsoft.com/sql_pfe_blog/2013/06/27/lazy-log-truncation-clearing-of-sql-transaction-log-vlf-status-deferred/

I'm still worried that dm_db_log_space_usage is showing my log space continually being consumed but given most of what I posted is based on VLF status, this post doesn't reflect that so probably best to let it die...

Improve this answer

Your Answer

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

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