0

I'm investigating a database with a large transaction log. Looking at DBCC Loginfo I can see the database has 1200 VLF's, which is on the high side but not terribly concerning. What does worry me though is one one the VLF's is 13GB.

The majority of the VLF are 100mb (the autogrow size), which should probably be higher.

Is there a way to get rid of this large VLF and what the implications of having such a large VLF.

This is on SQL 2017 enterprise edition and the database in question is in an AG

Cheers

Alex

Improve this question

1 Answer 1

Reset to default
3

Is there a way to get rid of this large VLF [...]

The only way is to shrink the log to remove the VLF.

[...] what the implications of having such a large VLF.

It stays active for a long time and won't truncate. Whether that really has any effect on your server and workload is unknown. If there are large transactions, then it may have zero effect or if there are infrequent small transactions it may artificially keep the log size larger than it needs to be. Honestly, the 1200 VLFs is more concerning than the single 13 GB VLF in my opinion given availability groups and redo/recovery.

Improve this answer
2
  • If you had some down time, you could try removing the database from the AG, put it in simple mode, shrink the t-log on the primary (which should clear out the large vlf(s), then, place the db back in full recovery mode, and, then place it back in the AG.
    – rvsc48
    Commented Nov 22, 2023 at 16:09
  • And you would have killed every secondary replica which would now need to be reseeded as the switch to simple will obliterate your recovery forks. If the database is 50 GB that might be fine, larger it probably won't be.
    – Sean Gallardy
    Commented Nov 22, 2023 at 16:52

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.