In this ultimate guide, learn how to fix high VLF counts and shrink transaction logs in SQL Server to improve performance. Discover best practices for managing transaction log sizes and VLFs, and get your hands on a robust script to help you out. Boost your SQL Server's performance and optimize your database administration with these tips.
Introduction
Well, hello there! If you are a SQL Server DBA, you know how important it is to keep your database running smoothly. One of the common issues that we all face is a bloated transaction log and high VLF counts. These can cause performance issues, slow database backups, slow rollbacks, slow database startup, and slow recovery. Fear not! For I am here to guide you on how to get those transaction logs in check!
Table of Contents:
What are Transaction Logs?
Transaction logs are a crucial part of SQL Server databases. They record all modifications made to the data and objects in the database and are used for point-in-time recovery, replication, and high availability. Transaction logs are used to manage and control the transactions in the database, and they help SQL Server maintain the integrity and consistency of the data.
What are VLFs?
VLF stands for "Virtual Log File". These are the smallest unit of transaction log storage. They are created when the transaction log is initially created or when it grows. Each VLF has a fixed size, and the number of VLFs in a transaction log can affect performance.
The number of VLFs in a transaction log file depends on the initial size of the file and the auto-growth settings of the transaction log file. Each VLF has a header that contains information about the VLF, such as its size, state, and sequence number.
The following is an excerpt from the Microsoft documentation:
The Database Engine chooses the size of the virtual log files dynamically while it's creating or extending log files. The Database Engine tries to maintain a few virtual files. The size of the virtual files after a log file has been extended is the sum of the size of the existing log and the size of the new file increment. The size or number of virtual log files can't be configured or set by administrators.
Virtual log file (VLF) creation follows this method:
In SQL Server 2014 (12.x) and later versions, if the next growth is less than 1/8 of the current log physical size, then create 1 VLF that covers the growth size.
If the next growth is more than 1/8 of the current log size, use the pre-2014 method:
If growth is less than 64 MB, create 4 VLFs that cover the growth size (for example, for 1 MB growth, create 4 VLFs of size 256 KB).
In Azure SQL Database, and starting with SQL Server 2022 (16.x) (all editions), this is slightly different. If the growth is less than or equal to 64 MB, the Database Engine creates only 1 VLF to cover the growth size.
If growth is from 64 MB up to 1 GB, create 8 VLFs that cover the growth size (for example, for 512-MB growth, create 8 VLFs of size 64 MB).
If growth is larger than 1 GB, create 16 VLFs that cover the growth size for example, for 8-GB growth, create 16 VLFs of size 512 MB).
If the log files grow to a large size in many small increments, they'll have many virtual log files. This can slow down database startup, and log backup and restore operations. Conversely, if the log files are set to a large size with few or just one increment, they'll have a few very large virtual log files.
For more information on properly estimating the required size and autogrow setting of a transaction log, see the Recommendations section of Manage the size of the transaction log file.
For more information about the SQL Server transaction log architecture and VLFs, see the Transaction log physical architecture section of the SQL Server transaction log architecture and management guide.
Why do Transaction Logs become Bloated and VLFs become High?
Transaction logs can become bloated due to several reasons, such as uncommitted transactions, long-running transactions, delayed high-availability synchronizations, or lack of transaction log backups.
High VLF counts can result from frequent and/or inefficient auto-growth, or large transaction log file sizes. High VLF counts can cause slow recovery time, slow database startup time, and increased disk I/O.
How to Shrink Transaction Logs?
Shrinking transaction logs is a straightforward process that can be done through SQL Server Management Studio or T-SQL commands. However, it's essential to understand that shrinking the transaction log can cause performance issues and is not recommended as a regular maintenance task. This is because when the transaction log needs to grow again, that has a performance impact since SQL Server needs to zero-out all the data in the newly allocated space, and IFI (Instant File Initialization) doesn't help either because it does not affect transaction logs (this is improved in SQL Server 2022 if you set the auto-growth to exactly 64MB).
Therefore, it should only be done when necessary, such as when the transaction log has grown excessively due to a one-time event.
To shrink the transaction log, you can follow these steps:
How to Fix High VLF Counts?
Fixing high VLF counts involves managing the transaction log file size and ensuring an optimal number of VLFs. To maintain a healthy transaction log, you can follow these best practices:
Set an appropriate initial transaction log file size. The size should be as close as possible to the final size required (in order to reduce the likelihood of auto-growth entirely).
Configure a reasonable auto-growth size based on expected growth, using the increments needed to achieve optimal VLF distribution, and have a relatively large growth_increment value.
Regularly monitor and maintain transaction log sizes and VLF counts.
Microsoft themselves provides the following tips in the documentation:
To keep the total number of VLFs at a reasonable amount, such as a maximum of several thousand, you can reset the transaction log file to contain a smaller number of VLFs by performing the following steps:
Shrink the transaction log files manually.
Grow the files to the required size manually in one step using the following T-SQL script:
ALTER DATABASE <database name> MODIFY FILE (NAME='Logical file name of transaction log', SIZE = <required size>);After you set the new layout of the transaction log file with fewer VLFs, review and make necessary changes to the auto-grow settings of the transaction log. This ensures that the log file avoids encountering the same problem in the future.
Before you perform any of these operations, make sure that you have a valid restorable backup in case you encounter issues later.
To determine the optimal VLF distribution for the current transaction log size of all databases in a given instance, and the required growth increments to achieve the required size, you can use the following GitHub script to fix VLFs.
(Source)
The script Fix-VLFs.sql provided by the Tiger team at Microsoft is quite alright, and I don't mean to throw any shade at them... But it is rather old and not very efficient for large environments with many databases.
That's why I implemented my own variation of the script, making it much easier to shrink large transaction log files as well as significantly reduce the VLF count in the process. The script performs all the checks for you and it works at the whole instance level - performing the check for all accessible and relevant databases. The script also supports multiple versions of SQL Server. Both old and new.
The script is available at our Madeira Toolbox repository here:
The script accepts two parameters at the top of the script:
DECLARE
@MinVLFCountForAlert int = 300
, @RunRemediation varchar(10) = '$(RunRemediation)'The first parameter, @MinVLFCountForAlert, should be self-explanatory. It filters the output for transaction logs with the specified minimum number of VLFs.
The second parameter, @RunRemediation, when set to "Y", will cause the script to automatically run the remediation script (shrink+resize). Note that its default value is $(RunRemediation) which means that it supports being set as a SQLCMD variable.
Regardless, the remediation command(s) will be returned as part of a resultset.
Also, if you want a more "extreme" version of this script which can be used for mass-shrinking transaction logs across a large number of databases in a hurry, you can use this script:
Conclusion
In conclusion, maintaining a healthy transaction log is crucial for the smooth running of SQL Server databases. Bloated transaction logs and high VLF counts can cause performance issues, rollbacks, database startups, and database recoveries to be slower. By following the best practices outlined above and using the scripts I provided, you can manage your transaction logs and VLFs efficiently.
Remember, shrinking transaction logs should only be done when absolutely necessary, and regularly monitoring and maintaining your transaction logs can help prevent issues from occurring.
Happy chopping!