Blog

The SQL Server transaction log files are managed as singe files in SQL Server. But internally those files consist of several VLF’s, or Virtual Log Files.

Each time the log needs to grow, additional VLF’s are created.

Over time, if this happens a lot, there can be too many VLF’s.

There is however no hard limit to the optimal number of VLF’s, but a message will be shown in the SQL Server log if there are more than 1000 VLF’s:

Database xxx has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

According to this connect item the actual number of VLF’s when the message is shown is 10000.

So what are the problems with too many VLF’s? Many VLF’s means the log is internally fragmented, and that in turn makes all operations reading the log slower. Transaction log backup is one example.

If you want to know how many VLF’s there are in a database, just run DBCC LOGINFO:

The number of rows returned is the number of VLF’s.

To decrease the number of VLF’s, one solution is to shrink the log file to the minimum size and then increase it back to the desired size.

According to this blog post by Kimberly Tripp, if your log file needs to be large, there must not be too few VLF’s either since they then become too large. She recommends increasing the transaction log in several steps, each increment 8GB. So if the target size of the log file is 64GB, the following script can be used:

DBCC LOGINFO

DBCC SHRINKFILE(<LogFileName>);

ALTER DATABASE <DatabaseName> MODIFY FILE (NAME=<LogFileName>, SIZE = 8000);
ALTER DATABASE <DatabaseName> MODIFY FILE (NAME=<LogFileName>, SIZE = 16000);
ALTER DATABASE <DatabaseName> MODIFY FILE (NAME=<LogFileName>, SIZE = 24000);
ALTER DATABASE <DatabaseName> MODIFY FILE (NAME=<LogFileName>, SIZE = 32000);
ALTER DATABASE <DatabaseName> MODIFY FILE (NAME=<LogFileName>, SIZE = 40000);
ALTER DATABASE <DatabaseName> MODIFY FILE (NAME=<LogFileName>, SIZE = 48000);
ALTER DATABASE <DatabaseName> MODIFY FILE (NAME=<LogFileName>, SIZE = 56000);
ALTER DATABASE <DatabaseName> MODIFY FILE (NAME=<LogFileName>, SIZE = 64000);

More information on VLF’s can be found in the links below:

8 Steps to better Transaction Log throughput by Kimberly Tripp
Transaction Log VLFs – too many or too few? by Kimberly Tripp
SQL Server Transaction Log Fragmentation: a Primer by Greg Larsen
Performance impact: a large number of virtual log files – Part I by Linchi Shea