SQL Server 2022 – Managing Virtual Log Files

Virtual Log Files (VLFs) are internal divisions of physical log files created by the SQL Server Database Engine. They have no fixed size, and the number of virtual log files for a physical log file is not fixed. The creation of VLFs is determined by the Database Engine during the creation or extension of log files. The size of virtual log files is calculated dynamically by summing the existing log’s size and the new file increment. However, too many VLFs can cause several problems, including slow database startup and log backup and restore operations. The more VLFs present, the longer the initial database recovery process can lead to timeouts, memory-related errors, and other related issues.

Optimal VLF Distribution in 2022

Virtual Log Files (VLFs) are created in SQL Server to manage transaction logs. In previous versions, if the log file grew by less than 1/8 of its current size, only one VLF would be created to cover the growth size. If the growth was larger, multiple VLFs would be created based on the size of the growth increment. However, in SQL Server 2022, a new method is used. If the growth is less than or equal to 64 MB, only one VLF is created to cover the growth size. For growth between 64 MB and 1 GB, 8 VLFs are created, and for growth larger than 1 GB, 16 VLFs are created to cover the growth size. This new method is designed to optimize the creation of VLFs and improve database performance.

Monitoring VLFs

Here is an example of how to use SQL code to check the number of virtual log files (VLFs) in a database:

USE YourDatabaseName;
GO
DBCC LOGINFO;

This command will display information about the VLFs in the current database, including the number of VLFs and their status. The output will include a row for each VLF, with columns such as FileId, FileOffset, and Status. The Status column will indicate whether the VLF is active (2), inactive (0), or a part of a partially used VLF (1).

You can also use the following SQL code to monitor the number of VLFs over time:

SELECT name, log_reuse_wait_desc, database_id,
log_reuse_wait, log_size_in_bytes/1024/1024 AS log_size_mb,
log_size_in_bytes/1024/1024/log_file_count AS avg_vlf_size_mb, 
log_file_count
FROM sys.dm_db_log_space_usage;

This query will return information about the log file used for each database on the server, including the number of VLFs and the average size of each VLF. The log_reuse_wait_desc column will indicate why the log file is not being reused, such as waiting for a backup or waiting for a checkpoint. You can use this information to identify databases that may have too many VLFs or other issues with log file usage.

Fixing VLFs Issue

Here is an SQL code example to fix the issue of too many VLFs in SQL Server:

— Shrink the transaction log file manually

DBCC SHRINKFILE('<logical file name of transaction log>', TRUNCATEONLY);

— Grow the transaction log file to the required size manually

ALTER DATABASE <database name> 
MODIFY FILE (NAME='<logical file name of transaction log>', 
SIZE = <required size>);

— Review and modify auto-grow settings of the transaction log file

ALTER DATABASE <database name> 
MODIFY FILE (NAME='<logical file name of transaction log>', 
FILEGROWTH = <new growth size>);

Note that you should replace <logical file name of transaction log>, <database name>, <required size>, and <new growth size> with the appropriate values for your specific case. Also, as mentioned before, ensure you have a valid restorable backup before performing any of these operations.

Best Practices

It’s important to note that you should follow best practices when managing VLFs to avoid performance issues. Some best practices include:

• Size your log files appropriately based on your workload to avoid frequent growth events.
• Set the growth_increment value to be less than or equal to 64 MB to reduce the number of VLFs created during growth events.
• Monitor your VLFs regularly using the provided SQL queries to identify issues, such as too many VLFs or long recovery times.

In conclusion, managing VLFs is a critical aspect of SQL Server database administration that can significantly impact database performance. With the enhancements made in SQL Server 2022 and the use of best practices, administrators can optimize VLF distribution and reduce the number of VLFs, leading to improved database startup times, faster backup and restore operations, and a better overall experience for end-users.

You can always connect with me on Twitter.

Reference: Pinal Dave (http://blog.SQLAuthority.com)