Virtual Log Files in SQL Server: Understanding VLFs And Its Role in SQL

As database administrators, we are all well aware of the transaction logs in SQL Server. However, not all of us are well aware of the internal structure of LDF files, which includes Virtual Log Files in SQL Server. So, with the help of this technical write-up, we will learn thoroughly about these VLFs and all the crucial factors a user must know to deal with the challenges of these files. 

But, before going straight to the technicalities, let’s understand these files briefly to ensure a complete understanding of the concept to the users. 

What is VLF in SQL Server?

When we talk about Virtual Files in SQL Server(VLFs), they can simply be defined as the smaller internal segments of the LDF file. This segment in the transaction log file is generally used by the database engine for tracking and managing transactions in SQL Server. This helps the server to record the transactions in multiple VLFs instead of one large block, making it efficient to store and maintain the transaction entries. 

If we talk about how these VLFs work, the answer is quite simple. The transaction logs in SQL Server mainly operate in a circular format, adding the transaction records in the available virtual log files. However, when all the available VLFs are occupied or full, the SQL Server further increases the size of the LDF file and new virtual log files in SQL Server are created. As we now know a little about these virtual files, let’s now take a look at why these files are crucial for database performance.

How VLFs in SQL Server Impacts Database Performance?

Earlier we learned that these virtual log files are nothing but smaller chunks of the transaction log files in SQL Server. Now to understand the role of these files more clearly, we will see what these files are actually responsible for.

• The VLFs that are active stores the transaction records that are required by the SQL Server for recovery purposes, or SQL Server mirroring/replication process. 
• The virtual log files that are inactive are reusable by the server once the SQL Server inspects and confirms that the specified VLFs are no longer being used or required.
• To ensure the continuity aross these files, the Log Sequence Numbers (LSNs) are used. This further allows the SQL Server maintaining a consistent state during recovery after a server crash or while performing restore operations.

To sum up the roles, we can conclude how VLFs are helpful in SQL Server in managing the transaction records and in the situation of server crashes or corruption. But when the VLF count increases from a specified number, they become harmful for the database as well. We will now take a look at how these small chunks of transaction log files create bigger challenges for the database administrators.

Too Many Virtual Log Files in SQL Server? Why High VLF Count Is Bad

Even when these files offer multiple benefits to the SQL Server users and database administrators, they also lead to different issues when the number of these files increases. Here are some of the issues that occur due to high VLF count in SQL Server:

• With a high number of SQL Server virtual log files, the startup process of the database might get affected and become slower. This can happen because the server has to read all the VLFs present in the server. 
• The high number of VLFs can also impact the backup or restore process of the transaction log files in SQL Server. This will result in increasing the time required process. 
• Any operation that requires reading the transaction log files might be hugely affected with too many virtual log files in SQL Server and further experience delays during the processes. This can further lead to performance degradation of the database. 
• Most of the operations stored in the LDF files in SQL Server database are INSERT, UPDATE, and DELETE commands. With the high count of virtual log files in the database, these commands can be affected. 

So what can we do to avoid such issues? Let’s take a look at the steps that can help the users and database administrators to check the VLF count in SQL Server and further find ways that can help reduce these files. 

How to Check VLF Count in SQL Server? Explained

To check the virtual log files in SQL Server count, database administrators can use the T-SQL commands. These commands allow the users to check the number of VLFs present in the specified database. We will now take a look at the command and understand how it works.

To check VLF Count For SQL Server for Version 2016 SP2 and Later, use the command:

SELECT 
name AS [Database_Name], 
COUNT(li.database_id) AS [VLF Count] 
FROM sys.databases AS s 
CROSS APPLY sys.dm_db_log_info(s.database_id) AS li 
GROUP BY s.name 
ORDER BY [VLF Count] DESC;

This command will allow you to check the number of virtual log files in the SQL Server database. In SQL Server 2016 SP2 and later, the Dynamic Management Function allows users to fetch a detailed report of the Virtual Log Files in SQL Server.  

To check VLF count in an older version of SQL Server, use the given command:

DBCC LOGINFO;

This command helps the database administrators to fetch the details about the SQL Server virtual log files in the transaction log file. 

After learning the commands on how to check VLF count in SQL Server, we will now proceed with the ways that will help with fixing high virtual log file count in the SQL database. 

Best Ways to Fix High VLF Count in SQL Server Explained

Here are some troubleshooting ways that will help reduce the number of virtual log files in SQL Server. Once the high count of VLF is detected, the general way is to reset the transaction log in SQL Server. Given below are the steps that will allow the users to reduce the VLF count in the SQL Server database. 

1. The first step is to check and identify the SQL Databases with a high number of virtual log files. This can be efficiently done with the help of the commands mentioned above. 
2. The next step is to identify the cause of the high count of VLF in SQL Server. The common causes for the increase of VLF count are:
   • The transaction log file (LDF) in SQL Server was allowed to grow in many small increments. ‘
   • The settings used for autogrowth in SQL Server led to the creation of many small virtual log files in the database. 

Fixing High VLF Count in SQL Server

Once the cause for the high VLF count in the SQL Database is detected, it’s time to take appropriate steps to resolve the issue. Here are some of the ways to fix the issue:

• One of the ways to fix the issue is to perform a log backup in the database. The databases in Bulk-Logged or Full recovery model, by taking a backup of the transaction log, will help to clear the inactive portions. This will further help with reducing the VLF count in the database. 
• By manually shrinking the transaction log file(LDF) in SQL Server, the size of the log file will be reduced, further resetting the virtual log files in SQL Server. This can be efficiently done with the help of the DBCC SHRINKFILE command. 
• Apart from shrinking the log file, growing the size of the log file can also help with the issue of a high count of VLF in SQL Server. The command given below will help with this process:
  ALTER DATABASE…MODIFY FILE
  This command will help to increase the size of the SQL log file up to a desired size. 
• The next way is to change autogrowth settings in SQL Server. Configuring the autogrowth settings to a larger size can prevent the transaction log file from growing frequently in small chunks. 

What if the VLF Files in SQL Server Get Corrupted?

There might be chances that entire database performance is affected not only because of the high VLF count but the issue being combined with VLF file corruption. This can cause errors like SQL Error 823 or SQL Server Error 824. In such cases, it becomes crucial for the database administrators to find a trusted and professional solution to repair the corruption in the transaction log file and further repair the errors caused due to the corruption in virtual log files in SQL Server. One reliable solution to repair this issue is the SysTools SQL Log Analyzer Tool.

This tool allows scanning the LDF file thoroughly for any corruption. Also, it helps further repair the LDF file while maintaining the data integrity throughout. 

Prevention Measures For High Count Virtual Files in SQL Server

• It is a safer practice to pre-size the log files as per the requirement instead of leaving small files.
• Next is to set the log file growth in whole numbers instead of percentages. This will save the log files from growing to large sizes. Also, it can avoid any sudden increase in virtual log files in SQL Server.
• Regularly monitoring the VLF counts in SQL Server will help with early actions in case of increasing VLF counts. 

These steps will help the database administrators to understand the status of the virtual log files in the database. Furthermore, they can prepare for any issues before they lead to bigger damages in the database. 

Conclusion

Through this article, we have thoroughly learned about virtual log files in SQL Server. We have also discussed its roles and the issues they might possibly create. Additionally, to resolve the issue in a seamless way and avoid any damage to the database. Lastly, we have suggested the best troubleshooting ways to reduce the count of VLF in SQL Server.