0

Is there an ideal size for the VLFs in a transaction log. This is a system with AlwaysON and Transactional replication. I was wondering if there is any cons to creating large VLF's. Eg, lets say we create a database and expand the log to 96GB. So based on the calculation, the expanded log will create 96/16, 6GB VLF's. Other than it can only truncate the log to 6GB blocks, is there any negatives to having large VLF's, especially from a performance perspective?

Improve this question

1 Answer 1

Reset to default
1

According to Paul Randal's article, the only negative side is that indicated already in you question,

You might think that it could lead to very large VLFs (e.g. you set a 4 GB auto-growth size with a 100 GB log), and it can. But so what? Having very large VLFs is only a problem if they’re created initially and then you try to shrink the log down. At a minimum you can only have two VLFs in the log, so you’d be stuck with two giant VLFs at the start of the log and then smaller ones after you’d grown the log again. That can be a problem that prevents the log being able to wrap around and avoid auto-growth, but that’s not anywhere near as common as having too many VLFs. And that’s NOT a scenario that the new algorithm creates.

And your system that has replication and Always On can suffer only from "VLF fragmentation", i.e. "too many VLFs" as every time the server needs to find any particular LSN it has to traverse all the VLFs starting with active VLF.

Improve this answer
2
  • Why would it prevent the log being able to wrap around and avoid auto growth? Didn't understand that part.
    – DMDM
    Commented Feb 27 at 3:14
  • @DMDM Maybe it should sound like this: if you have only 2 giant VLFs, in case of long running transaction, it can fill up some last log records within the first VLF and continue in the second, in this case the log cannot wrap around as the first VLF even if it has only few active records cannot be cleared so it will cause the log to grow, even if the first VLF in your case will have almost 6Gb free. But I don't think you'll ever want to truncate your log to leave only 2 VLFs
    – sepupic
    Commented Feb 27 at 9:11

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.