What’s the issue?
SQL Server divides every transaction log file into smaller internal segments called Virtual Log Files (VLFs). The number and size of VLFs created during a log file growth depends on the size of the growth increment: small growths produce many small VLFs, while large growths produce fewer, larger VLFs.When a log file has been grown many times in small increments, often through default autogrowth settings or after frequent shrink and grow cycles, the total VLF count can climb into the thousands. This finding flags databases where the number of VLFs in the transaction log exceeds 200, which is our recommended threshold for investigation.
Why is this a problem?
A high VLF count adds overhead to nearly every operation that touches the transaction log. Log backups slow down because SQL Server must process each VLF individually, transaction log replay during recovery takes longer (extending crash recovery and Always On failover times), and database startup itself can become noticeably slower.The performance impact compounds during disaster recovery. A database that takes seconds to come online with a properly sized log can take many minutes or longer with thousands of VLFs, directly extending downtime when it matters most.
Excessive VLFs also indicate poor log management practices, typically a combination of small autogrowth increments, undersized initial log files, and possibly repeated shrink operations. The VLF count itself is a symptom, but the underlying configuration usually causes other related problems including log growth pauses and unpredictable performance.
What should you do about this?
Identify the VLF count for each database by running DBCC LOGINFO against the database, or use sys.dm_db_log_info (available in SQL Server 2016 and later) for a set based view across databases. Counts above 200 warrant attention, and counts in the thousands should be addressed promptly.
To rebuild the log with fewer, larger VLFs, first take a transaction log backup to ensure the log is truncatable, then shrink the log to a small size with DBCC SHRINKFILE (LogicalLogFileName, TargetSizeMB);. After shrinking, manually grow the log back to its proper operational size in one or two large increments using ALTER DATABASE [DatabaseName] MODIFY FILE (NAME = N’LogicalFileName’, SIZE =
Set the autogrowth value to a fixed, sensible size such as 64 MB rather than a percentage or small fixed value, so future growths do not recreate the problem. Size the log file proactively for the workload (including index maintenance and large transactions) so autogrowth events become rare.
Read more…
SQL Server Transaction Log Architecture and Management Guide – SQL Server | Microsoft Learn