What are SQL Virtual Log Files aka SQL Server VLFs?

In this article we’ll review SQL Virtual Log files aka SQL Server VLFs, providing an overview and review topics related to size, performance, monitoring and troubleshooting.

In a previous article of this series, SQL Server Transaction Log Architecture, we described the internal architecture of the SQL Server transaction log, why and how the SQL Server Engine uses the transaction log and finally how the transaction log truncation process occurs. In this article, we will discuss the Virtual Log File concept in-depth.

Virtual Log File Overview

In SQL Server, each transaction log is logically divided into smaller segments, in which the log records are written. These small segments are called SQL Virtual Log Files, also known as VLFs. When the transaction log file is created or extended, the number of SQL Server VLFs in the transaction log and the size of each Virtual Log File are determined dynamically. On the other hand, the size that is determined for the first Virtual Log File will be used for all newly created SQL Virtual Log Files on the same transaction log.

When a new log record is written, the SQL Server Engine will use the existing unused SQL Server VLFs. If all the existing SQL Virtual Log Files are active and the transaction log file extend is required, new SQL Server VLFs will be created based on the size of the transaction log to be written.

Transaction Log File Size

When a new SQL user database is created, two database files will be created, a database data file, with MDF extension, in which the tables data will be written and a database log file, with LDF extension, in which all the database transactions will be written for recovery purposes.

By default, the initial size and the auto-growth settings for the transaction log file will meet the initial size and auto-growth settings of the model system database. For example, in SQL Server 2016, the initial size of the transaction log file is 8MB, that can be increased with the auto-growth amount of 64MB, with no limitation for the maximum file size, as shown below:

The default values of the initial size and auto-growth settings of the transaction log file can be modified during the database creation process, or later when the database is already created, from the database properties page, based on your database growth plan.

The auto-growth option of the database files is enabled by default. The auto-growth option of the transaction log file should be controlled from different aspects. The first aspect is specifying a limit to the maximum size that the transaction log file can reach. In this case, you will protect the hosting disk drive from running out of free space due to the transaction log file growth and protect the database from being inaccessible.

The second aspect of managing transaction log file growth is specifying a reasonable initial size and auto-growth amount or percent. There is no optimal value for transaction log file initial size and auto-growth that can fit all situation. But what should be considered is specifying a proper value for these two settings that prevent the small and frequent increases in the size of the transaction log file. This is due to the fact that the SQL Server Engine will pause all log writing processes until the log file size increase process is completed, causing serious performance issues.

SQL Server allows us to create more than one transaction log file on each database, but this option is not recommended, as the database transaction logs are written in sequential order into the transaction log. In this case, the SQL Server Engine will not start writing to the second transaction log file until the first file is full. The ability of creating more than one transaction log file on the database can help only when you plan to extend the transaction log file size and the current hosting disk drive runs out of free space.

Virtual Log File Size

In SQL Server, there is no option to set the number or the size of the SQL Virtual Log Files manually, as the SQL Serve Engine determines the size dynamically when the transaction log file is created at the first time or extended when extra size is required.

The number of SQL Server VLFs is increased as a response to the transaction log file auto-growth event, where extra SQL Virtual Log Files will be created on the transaction log to provide a location for the new transactions to be written. On the other hand, the number of SQL Virtual Log Files is decreased by performing a file shrink process on the transaction log file, to release the space back to the operating system, resulting smaller transaction log file.

The truncation process differs from the shrink process in that, the space will be released for a new transaction log to be written in the transaction log file, in the case of truncation, where the space will be released back to the operating system in the case of shrink. The Virtual Log File is the smallest unit of truncate in the transaction log file, where the Virtual Log File will be truncated only if it contains no single active record.

Virtual Log File and Performance

Having a large number of SQL Virtual Log Files in the transaction log file can lead to performance issues. The SQL Server Engine tries internally to keep the number of SQL Server VLFs as small as possible, as it can process the smaller number of these VLFs more efficiently, specially during the crash and recovery conditions. When the SQL Server service is restarted, the database will be in recovery state, in which the SQL Server Engine reads the SQL Virtual Log Files in order to maintain the databases in consistent state, by rolling forward the committed transactions and rolling back the failed ones. If the transaction log file contains large number of SQL Server VLFs, the SQL Server Engine will take long time recovering the database transitions.

The large number of the SQL Virtual Log Files results from small initial size and small auto-growth amount of the transaction log file. In this case, the transaction log file will grow very frequently in small chunks each time an extra space is required. As a result, the transaction log file will consist of large number of small SQL Server VLFs, that will result in performance degradation issues. To overcome such issue, it is highly recommended to set the initial size of the transaction log file to a sufficiently large amount that you think it fits your database transaction log growth with large auto-growth value, not less than 1GB for heavily transactional databases.

Monitoring SQL Virtual Log Files

As a proactive database administrator, it is a very important task, but unfortunately missed by many, to monitor the number of the SQL Server VLFs on your databases, and make sure that it in the acceptable range, for example less than 50 VLFs per each 10GB, that will not affect the performance of your databases negatively.

The number of SQL Virtual Log Files on the database can be easily monitored using the DBCC command below, that returns one record for each VLF, as follows:

1	DBCC LOGINFO

And the result from a newly created database, will show a small number of SQL Server VLFs, as below:

If we change the default auto-growth amount for the transaction log file of our database, for testing purposes, from 64MB to 1MB as follows:

Then insert 1M records to the table, then check the SQL Virtual Log Files number, using the DBCC command, you will see that the number of SQL Server VLFs increased to 89, due to the large number of small increments that are performed on the transaction log file. You can imagine the time required to recover the database after a crash or reboot process:

Fixing SQL Virtual Log Files Issues

We mentioned previously how to be a proactive database administrator and prevent the issue of having large number of SQL Server VLFs in your database. But what should be done if the issue is occurring now, as we saw in the previous example?

In this case, you can fix it by simply shrinking the transaction log file and re-grow it again with a suitable initial size and large auto-growth amount, during non-peak time and after making sure that there is no heavy transaction is running, by following the steps below:

Perform a transaction log backup for the database to truncate the inactive part of the transaction log. This is valid only if the database recovery model is FULL and can be skipped to step 3 if the database recovery model is simple.
Perform a manual CHECPOINT in order to write all the pages in the buffer to the database files.
Shrink the database log file to the smallest possible size.
Once the number of SQL Virtual Log Files is reduced:

Set the initial size of the transaction log file to a larger suitable size and the auto-growth option to a higher amount that prevents the frequent small increments and protect your database from the high SQL Server VLFs number issue as shown below:

In the next article, we will discuss the relationship between the SQL Server transaction log and database recovery models. Stay tuned!