SQL Server too many VLFs: slow startup, slow restores and lagging AG redo, and how to rebuild the log

#The symptom

One database takes far longer than the others to come online after a restart or failover. It sits in In Recovery for minutes. Restores of that database spend most of their time after the data copy finishes. On an Availability Group secondary the redo queue drains slowly after a large transaction, even though CPU and disk look idle.

A common shared cause is a transaction log split into thousands of tiny virtual log files (VLFs). The log was created small and then grew in small autogrowth steps, often the old 10% or 1 MB defaults, thousands of times. Recovery, log backups, restores and redo all walk the VLF list, so a long list slows every one of them down.

The error log may also show a startup warning that the database "has more than 1000 virtual log files which is excessive".

#Check: how many VLFs, and how big

sys.dm_db_log_info returns one row per VLF. Run this across every database on the instance:

SELECT  d.name,
        COUNT(*)                         AS vlf_count,
        SUM(CAST(li.vlf_active AS int))  AS active_vlfs,
        CAST(MIN(li.vlf_size_mb) AS decimal(10,2)) AS min_vlf_mb,
        CAST(MAX(li.vlf_size_mb) AS decimal(10,2)) AS max_vlf_mb,
        CAST(SUM(li.vlf_size_mb) AS decimal(12,0)) AS log_size_mb
FROM    sys.databases AS d
CROSS APPLY sys.dm_db_log_info(d.database_id) AS li
WHERE   d.state_desc = 'ONLINE'
GROUP BY d.name
ORDER BY vlf_count DESC;

sys.dm_db_log_stats gives the summary plus the reason the log can't be truncated, which you need before you shrink:

SELECT  DB_NAME(ls.database_id) AS db,
        ls.total_vlf_count,
        ls.active_vlf_count,
        ls.total_log_size_mb,
        ls.active_log_size_mb,
        ls.log_truncation_holdup_reason,
        ls.log_backup_time
FROM    sys.databases AS d
CROSS APPLY sys.dm_db_log_stats(d.database_id) AS ls
ORDER BY ls.total_vlf_count DESC;

Check the growth setting that caused it:

SELECT DB_NAME(database_id) AS db, name, type_desc,
       size * 8 / 1024 AS size_mb,
       CASE is_percent_growth WHEN 1 THEN CONCAT(growth, '%')
            ELSE CONCAT(growth * 8 / 1024, ' MB') END AS growth
FROM   sys.master_files
WHERE  type_desc = 'LOG'
ORDER BY db;

#How to read the numbers

There is no exact limit. Treat a few thousand as a problem worth fixing in the next maintenance window, and tens of thousands as the likely cause of a slow recovery you are seeing now.

#Why the count gets high

Each log growth adds a set of VLFs. On SQL Server 2014 and later the engine decides roughly like this (check the "Transaction log architecture" page in the docs for your version):

So a 50 GB log that grew from 1 MB in 10% steps, or one that was shrunk to nothing every night by a maintenance plan and regrew all day, ends up with thousands of VLFs.

#Fix: shrink the log, then grow it back in planned steps

Warning

Do this in a quiet period. Shrinking and regrowing the log is fully logged and blocks while the file is zeroed out (log files cannot use instant file initialization, except growths up to 64 MB on SQL Server 2022). Don't shrink a log that the next batch window will grow straight back.

#1. Decide the target size

Size the log for its real peak, usually the largest index rebuild or batch load plus headroom. A reasonable estimate is the highest total_log_size_mb you have seen, or the peak used_log_space_in_percent from monitoring.

#2. Get the active part of the log to the front

In FULL or BULK_LOGGED recovery, take a log backup so the inactive VLFs can be reused:

BACKUP LOG Sales TO DISK = N'\\backup01\sql\Sales_log_pre_shrink.trn' WITH COMPRESSION, CHECKSUM;

In SIMPLE recovery, run CHECKPOINT; in the database instead.

Caution

Never switch the database to SIMPLE recovery to make the shrink easier. That breaks the log backup chain and removes point-in-time recovery until the next full backup.

#3. Shrink the log file

Find the logical name, then shrink as small as it will go:

USE Sales;
SELECT name, size * 8 / 1024 AS size_mb FROM sys.database_files WHERE type_desc = 'LOG';

DBCC SHRINKFILE (N'Sales_log', 1);   -- target in MB; it stops at the last active VLF

The shrink can only remove VLFs after the last active one. If the count barely moves, check which VLFs are active:

SELECT vlf_sequence_number, vlf_size_mb, vlf_active, vlf_begin_offset
FROM   sys.dm_db_log_info(DB_ID('Sales'))
ORDER BY vlf_begin_offset;

If the active VLF sits near the end of the file, take another log backup (or checkpoint), wait for activity to wrap to the start of the file, and shrink again. Two or three rounds is normal on a busy database.

#4. Grow the log back to the target in chunks

Grow manually so each step creates a small number of large VLFs:

ALTER DATABASE Sales MODIFY FILE (NAME = N'Sales_log', SIZE = 8192MB);
ALTER DATABASE Sales MODIFY FILE (NAME = N'Sales_log', SIZE = 16384MB);
ALTER DATABASE Sales MODIFY FILE (NAME = N'Sales_log', SIZE = 24576MB);
-- continue until you reach the target

Steps of 8 GB give 16 VLFs of about 512 MB each per step. That is a sensible size for most production logs.

#5. Set a fixed autogrowth

ALTER DATABASE Sales MODIFY FILE (NAME = N'Sales_log', FILEGROWTH = 1024MB);

Re-run the VLF query and confirm the count is now in the tens or low hundreds.

#Availability Groups

Run the shrink and the resize on the primary. They are logged operations and replay on every secondary. Log truncation on the primary waits for all secondaries to harden the log. If log_truncation_holdup_reason shows AVAILABILITY_REPLICA, fix the lagging secondary before you try to shrink. Run the steps on one database at a time and watch the send and redo queues between steps.

#Amazon RDS for SQL Server

The DBCC SHRINKFILE and ALTER DATABASE ... MODIFY FILE statements work with the master user on RDS. Log backups are managed by RDS, so you can't run step 2 yourself. Do the work after an automated log backup, or open a support case if the active portion of the log won't move.

#Do it across many databases with dbatools

If you have the dbatools PowerShell module, you can measure the whole estate in one pass:

Import-Module dbatools
Measure-DbaDbVirtualLogFile -SqlInstance sql01, sql02 |
    Where-Object Total -gt 1000 |
    Sort-Object Total -Descending |
    Select-Object SqlInstance, Database, Total, Active, Inactive |
    Format-Table -AutoSize

Output properties can vary between dbatools releases. Check Get-Help Measure-DbaDbVirtualLogFile -Full on your version.

#Prevent it next time

• Create new databases with the log pre-sized for the expected peak and a fixed MB growth. Set the same on model so new databases inherit it.
• Remove any maintenance plan step or job that shrinks log files on a schedule.
• Keep log backups frequent enough that the log never has to grow to absorb normal activity.
• Add the VLF count query to your weekly health check and alert on any database above a threshold you choose, for example 1,000.
• After any emergency log growth (for example after error 9002), check the VLF count once the incident is closed and rebuild the log if it jumped.