Virtual Log Files (VLF) is an internal mechanism for SQL Server to work with the transaction log (mostly for truncation). You shouldn’t have a large number of VLFs as it can lengthen recovery time and can negatively affect performance of your database. Ideally a lower count of VLF lis considered better than a larger number because the number of VLFs can impact overall database performance.
We find that the formula for how VLFs are created has changed in SQL Server 2014, and this article from Paul Randal helps us understand how the process works.
The change is pretty profound, and is aimed at preventing lots of auto-growth from creating huge numbers of VLFs. This is cool because having too many (it depends on the log size, but many thousands is too many) VLFs can cause all manner of performance problems around backups, restores, log clearing, replication, crash recovery, rollbacks, and even regular DML operations.
Up to 2014, the algorithm for how many VLFs you get when you create, grow, or auto-grow the log is based on the size in question:
- Less than 1 MB, complicated, ignore this case.
- Up to 64 MB: 4 new VLFs, each roughly 1/4 the size of the growth
- 64 MB to 1 GB: 8 new VLFs, each roughly 1/8 the size of the growth
- More than 1 GB: 16 new VLFs, each roughly 1/16 the size of the growth
So if you created your log at 1 GB and it auto-grew in chunks of 512 MB to 200 GB, you’d have 8 + ((200 – 1) x 2 x 8) = 3192 VLFs. (8 VLFs from the initial creation, then 200 – 1 = 199 GB of growth at 512 MB per auto-grow = 398 auto-growths, each producing 8 VLFs.)
For SQL Server 2014, the algorithm is now:
- Is the growth size less than 1/8 the size of the current log size?
- Yes: create 1 new VLF equal to the growth size
- No: use the formula above
So on SQL Server 2014, if you created your log at 1GB and it auto-grow in chunks of 512 MB to 200 GB, you’d have:
- 8 VLFs from the initial log creation
- All growths up to the log being 4.5 GB would use the formula, so growths at 1, 1.5, 2, 2.5, 3, 3.5, 4, 4.5 GB would each add 8 VLFs = 56 VLFs
- All growths over 4.5 GB will only create 1 VLF per growth = (200 – 4.5) x 2 = 391 VLFs
- Total = 391 + 56 + 8 = 455 VLFs
455 is a much more reasonable number of VLFs than 3192, and will be far less of a performance problem.