Transactional Log and its VLFs

Transactional Log and its VLFs

The transaction log is split up into chunks called virtual log files (VLFs) so the log management system can easily keep track of which portions of the transaction log are available for reuse. There is a formula for how many VLFs you get when you create your transaction log, manually grow it, or it auto-grows:

Up to 1MB	2 VLFs, each roughly 1/2 of the total size
1MB to 64MB	4 VLFs, each roughly 1/4 of the total size
64MB to 1GB	8 VLFs, each roughly 1/8 of the total size
More than 1GB	16 VLFs, each roughly 1/16 of the total size

For example, if you create a transaction log to be 8GB you’ll get 16 VLFs where each is roughly 512MB. If you then grow the log by another 4GB, you’ll get an additional 16 VLFs with each being roughly 256MB, for a total of 32 VLFs.

A general best practice is to set the log auto-growth to something other than the default 10%, so that you can control the pause that’s required when zero-initializing new transaction log space. Let’s say you create a 256MB transaction log and set the auto-growth to 32MB, and then the log grows to a steady-state size of 16GB. Given the formula above, this will result in your transaction log having more than 4,000 VLFs.

This many VLFs will likely result in some performance issues for operations that process the transaction log (e.g. crash recovery, log clearing, log backups, transactional replication, database restores). This situation is called having VLF fragmentation. Generally any number of VLFs more than a thousand or so is going to be problematic and needs to be addressed (the most I’ve ever heard of is 1.54 million VLFs in a transaction log that was more than 1TB in size!).

The way to tell how many VLFs you have is to use the undocumented (and completely safe) DBCC LOGINFO command. The number of rows of output is the number of VLFs in your transaction log. If you think you have too many, the way to reduce them is:

1.      Allow the log to clear

2.      Manually shrink the log

3.      Repeat steps 1 and 2 until the log reaches a small size (which may be tricky on a busy production system)

4.      Manually grow the log to the size it should be, in up to 8GB steps so each VLF is no larger than about 0.5GB