VLF

What are virtual log files – VLF?

Each SQL Server transaction log file is made of smaller parts called virtual log files. The number of virtual log files is not limited or fixed per transaction log file. Also, there is no fixed size of virtual log file, if one is e.g. 512 kilobytes, all other will be of the same size. 

SQL Server determinates the size of a virtual log file dynamically when the transaction log file is created or extended. The goal is to maintain the small number of the virtual log files in the transaction log file, because SQL Server handles the smaller number of files easier. The size or number of virtual log files can’t be configured or set by a database administrator. 

By default, the SQL Server transaction log file is set at an initial size of 2MB. Also, the default growth value is 10% of the current size. While creating a SQL Server database, these options can be modified to accommodate planned needs for the database. The auto-growth option is optional and turned on by default. File growth can be specified in megabytes or percent. There is also the clause to limit the maximum file size. By default, SQL Server creates a database with unrestricted file growth.

If the auto-growth settings are not properly managed, a SQL Server database can be forced to auto-grow, which can cause serious performance issues. SQL Server will stop all processing until the auto-grow event is finished. Due to physical organization of the hard drive, the auto-growth event will take up the space which is not close physically to the previous one occupied by the transaction log file. This leads to the physical fragmentation of the files which also causes slower response.

There is no general rule how to determine the best values for the auto-growth option, as these vary from case to case. Having too many or too little virtual log files causes bad performance.

There isn’t an option in SQL Server Management Studio which can provide the number of virtual log files. The virtual log files can be shown via T-SQL script for each SQL Server database. There will be other blog post which describes this topic in detail.

The number of virtual log files can be increased by an auto-grow event, this process is common, but it needs strict rules to avoid unplanned problems with space or unresponsiveness in peak hours. The number of virtual log files can be decreased by shrinking the SQL Server transaction log file, which also requires strict rules to avoid deleting the data which hasn’t been backed up yet.