SQL Server high VLF count: transaction log fragmentation that slows recovery

A database restarts and takes 45 minutes to come back ONLINE. An AlwaysOn failover completes in seconds, but the new primary sits in RECOVERING for an hour while the redo queue drains. The error log shows nothing obviously wrong: no 823 or 824, no corruption, no missing files. CPU and I/O during recovery look modest, just stretched out. Users escalate.

The hidden variable is often the Virtual Log File (VLF) count. SQL Server divides every database’s transaction log into VLFs, internal segments that the engine walks during crash recovery, log backup, and AG redo. Hundreds of small autogrowth events accumulated over months fragment the log into thousands or tens of thousands of these segments. Each one is a header to read, a range to verify, a step in the recovery scan.

This is a silent tax. High VLF count produces no error, no wait type, and no performance counter. It surfaces only when it costs the most: during the RECOVERING window after a restart or failover. By the time you notice, you have usually already failed over once too slowly.

For how the transaction log is reused and why it grows, see the SQL Server mental model guide. This article covers the VLF-specific failure mode: detection, interpretation, and consolidation.

What this means

The transaction log is not a single contiguous stream at the engine level. SQL Server slices it into VLFs. Each VLF has its own header, its own active or inactive state, and its own position in the log scan order. Recovery after a restart walks every VLF to locate the last checkpoint, identify active transactions, and roll forward or roll back. AG redo on secondaries walks VLFs as it applies log blocks. Log backups scan VLF headers to determine what changed since the last backup.

When the count is low (dozens to a few hundred), these scans are trivial. When the count climbs into the thousands or tens of thousands, per-VLF bookkeeping cost dominates. Restart recovery stretches from seconds to many minutes. Log backup duration grows. AG secondary redo falls behind, extending failover RTO and widening the redo queue window where a forced failover can lose data.

The growth pattern is almost always the same. A database with autogrowth enabled in small increments accumulates VLFs each time the log grows. SQL Server creates multiple VLFs per growth event. Hundreds of growth events over years produce thousands of VLFs. The log may be only a few GB on disk but internally fragmented into 8,000 segments.

These are operational thresholds, not Microsoft-specified hard limits.

Common causes

Quick checks

All read-only and safe.

-- VLF count for one database (SQL Server 2016 SP2+)
SELECT COUNT(*) AS vlf_count
FROM sys.dm_db_log_info(DB_ID('YourDatabase'));

-- VLF count for all user databases (SQL Server 2016 SP2+)
SELECT
    d.name AS database_name,
    COUNT(*) AS vlf_count
FROM sys.databases d
CROSS APPLY sys.dm_db_log_info(d.database_id)
WHERE d.database_id > 4
GROUP BY d.name
ORDER BY vlf_count DESC;

-- Pre-2016 SP2: use DBCC LOGINFO in the database context.
-- Each row returned is one VLF.
USE YourDatabase;
DBCC LOGINFO;

-- Permissions note:
--   sys.dm_db_log_info requires VIEW SERVER STATE (per the official reference)
--   on SQL Server 2016 SP2+ before 2022, and VIEW DATABASE PERFORMANCE STATE on
--   SQL Server 2022 and later.
<!-- verified: sys.dm_db_log_info permission is VIEW SERVER STATE (pre-2022), VIEW DATABASE PERFORMANCE STATE (2022+) -->
--   DBCC LOGINFO requires sysadmin.

-- Review autogrowth configuration for log files
-- growth is in 8KB pages when is_percent_growth = 0, percentage when is_percent_growth = 1
SELECT
    d.name AS database_name,
    mf.name AS logical_file_name,
    mf.size * 8 / 1024 AS current_size_mb,
    mf.growth AS growth_raw,
    mf.is_percent_growth
FROM sys.master_files mf
JOIN sys.databases d ON mf.database_id = d.database_id
WHERE mf.type_desc = 'LOG'
  AND d.database_id > 4
ORDER BY d.name;

-- Default trace: log file autogrowth events (event class 93)
-- Useful to confirm the fragmentation path from many small growth events.
DECLARE @trace_path NVARCHAR(500);
SELECT @trace_path = [path] FROM sys.traces WHERE is_default = 1;

SELECT
    DatabaseName,
    FileName,
    Duration AS duration_ms,
    IntegerData * 8 / 1024 AS growth_mb,
    StartTime
FROM sys.fn_trace_gettable(@trace_path, DEFAULT)
WHERE EventClass = 93
ORDER BY StartTime DESC;
<!-- verified: EventClass 93 = Log File Auto Grow; IntegerData = number of 8 KB pages by which the file increased (official event class reference) -->

-- Confirm whether the log can be truncated before any shrink attempt
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE database_id > 4;

How to diagnose it

1. Inventory VLF counts across all user databases. Use the all-databases query above. Anything above 1,000 goes on the at-risk list; anything above 10,000 is urgent.
2. Confirm the growth history. The default trace query above captures autogrowth events. A high VLF count with many small autogrowth entries confirms the fragmentation path.
3. Check log_reuse_wait_desc for each candidate database. The log must be truncate-able (ideally NOTHING) before a shrink can reclaim VLFs. If the wait is LOG_BACKUP, take a log backup first. If it is ACTIVE_TRANSACTION, find and address the open transaction before proceeding.
4. Estimate recovery impact. There is no authoritative per-VLF recovery time formula. The operational signal is the last RECOVERING duration. Compare databases of similar size: the one with the high VLF count is the one that took longer. Baseline each database’s recovery time so you can detect regression.
5. Correlate with AG redo performance. On secondary replicas, a high-VLF database that is also the redo bottleneck is a strong signal. The redo queue drains slowly even when secondary CPU and I/O look healthy.

flowchart TD
    A[Restart or failover] --> B[Database enters RECOVERING]
    B --> C{VLF count}
    C -->|Low: dozens to hundreds| D[Recovery in seconds to minutes]
    C -->|High: thousands| E[Recovery scan walks every VLF header]
    E --> F[Recovery takes many minutes to hours]
    F --> G[Extended downtime, AG redo backlog grows]
    G --> H[Forced failover may lose data]

Metrics and signals to monitor

Fixes

Consolidating VLFs requires shrinking the log to reclaim inactive VLFs, then regrowing it in a few large, controlled increments. This is disruptive. The shrink can block log activity for that database, and each regrowth step is itself an internal growth event. Schedule it in a maintenance window.

Before you start

• Take a fresh log backup on the database if it is in full or bulk-logged recovery. The log must be truncate-able before the shrink can reclaim VLFs.
• Confirm log_reuse_wait_desc returns NOTHING. If not, address the underlying cause first. A shrink on a non-truncate-able log reclaims nothing useful.
• Confirm you are in a maintenance window. The database remains online, but log activity can be blocked during shrink and during each regrowth step.
• If the database is in an AlwaysOn AG, expect secondaries to mirror the file size changes. Plan around replication.

Procedure

-- 1. Shrink the log file to reclaim inactive VLFs.
--    Replace logical_file_name with the log file name from sys.master_files.
--    Target a small size; we will regrow deliberately.
USE YourDatabase;
DBCC SHRINKFILE(logical_file_name, 64);
-- Verify the result. Shrink may not reduce VLFs if VLFs are still active.
-- If the count did not drop, take another log backup and retry.
SELECT COUNT(*) AS vlf_count_after_shrink
FROM sys.dm_db_log_info(DB_ID());

-- 2. Regrow the log in a few large fixed-size increments.
--    Each growth event creates a small number of VLFs.
--    Never use percent growth here.
ALTER DATABASE YourDatabase
MODIFY FILE (NAME = logical_file_name, SIZE = 8000MB);

ALTER DATABASE YourDatabase
MODIFY FILE (NAME = logical_file_name, SIZE = 16000MB);

ALTER DATABASE YourDatabase
MODIFY FILE (NAME = logical_file_name, SIZE = 24000MB);
-- Continue until you reach the target working size.

-- 3. Set autogrowth to one large fixed increment as a safety net.
--    Pre-sizing should handle normal load. Autogrowth is the last resort.
ALTER DATABASE YourDatabase
MODIFY FILE (NAME = logical_file_name, FILEGROWTH = 1000MB);

-- 4. Verify the VLF count dropped to a reasonable number.
--    Expect tens to low hundreds after consolidation.
SELECT COUNT(*) AS vlf_count
FROM sys.dm_db_log_info(DB_ID('YourDatabase'));

Why 8000MB increments

The traditional guidance is to regrow the log in fixed chunks of 8000MB rather than exact 4GB multiples. This avoids a historical VLF-sizing bug present in SQL Server versions prior to SQL Server 2012 SP1, where growth in exact 4GB multiples could produce incorrect VLF layouts. SQL Server 2012 SP1 and later fixed that bug, but 8000MB remains the conservative and portable choice.

The 4 GB-multiple VLF-sizing bug was fixed in SQL Server 2012 SP1; no SQL Server release supported in 2026 (2016 and later) is affected, but 8,000 MB remains the conservative, portable choice.

Tradeoffs

• Shrink can block log activity. Log writes for the database may be blocked during the operation. Do not run it during peak load.
• You must regrow to a sensible size. If you shrink to 64MB and leave it there, the log will autogrow back under load, recreating the problem or making it worse with small increments. Always pair shrink with deliberate regrow and a large fixed autogrowth.
• Do not leave autogrowth at a small fixed value or at percent. Small fixed growth creates the VLF problem you just fixed. Percent growth is unpredictable at scale.
• Do not make this a recurring task. Done correctly, with proper pre-sizing and a large fixed autogrowth, the consolidation is a one-time event. If VLF counts climb again, your autogrowth configuration is wrong, not the database.

Prevention

• Pre-size the log file. Size it for peak working volume between log backups (full recovery) or between checkpoints (simple recovery). The log should not autogrow during normal operations.
• Use a large fixed autogrowth increment. A single large increment produces a small number of VLFs per growth event. Avoid percent growth.
• Alert on autogrowth events. Any production autogrowth is worth attention. Log autogrowth blocks log writes during initialization and adds VLFs. Treat each event as a capacity signal, not a routine occurrence.
• Trend VLF count per database. A VLF count that is climbing means autogrowth is fragmenting the log. Catch it before the next unplanned restart exposes it.
• Baseline recovery time. Track how long each database spends in RECOVERING after a restart or failover. A rising baseline is the operational signal that VLF remediation is overdue.
• Check new databases. Application installers often create databases with default autogrowth settings. Correct the log sizing before the database accumulates history.

How Netdata helps

• Per-second metrics on log file size and percent used let you see autogrowth events as they happen, not after the VLF count has already climbed into the thousands.
• Correlate autogrowth events with recovery time. When a database’s RECOVERING window stretches, Netdata’s history shows the autogrowth pattern that produced the fragmentation. Netdata’s SQL Server (mssql) integration does not currently expose a database recovery-duration metric; the RECOVERING window itself is visible in the SQL Server error log or a custom query.
• Trend log usage and growth rate so you can pre-size logs proactively instead of reacting to autogrowth.
• Surface log_reuse_wait_desc alongside disk space and log usage. Knowing whether a log can be truncated is the precondition for any VLF consolidation work. Netdata’s SQL Server (mssql) integration does not currently expose log_reuse_wait_desc as a chart; query sys.databases alongside the Netdata views.
• AG redo queue and send queue metrics reveal when a high-VLF database is the secondary bottleneck, even before a failover exposes it.

Netdata’s Microsoft SQL Server monitoring brings these signals together with per-second metrics and anomaly detection.

Related guides

• SQL Server user connections climbing: connection pool leaks and retry storms
• SQL Server CPU utilization high: telling query load apart from a bad plan
• SQL Server CXPACKET and CXCONSUMER waits: parallelism, MAXDOP, and what is actually wrong
• SQL Server Error 9002: the transaction log for the database is full
• SQL Server high compilations per second: plan cache pollution and CPU burn
• How Microsoft SQL Server actually works in production: a mental model for operators
• SQL Server log backups missing: the full-recovery log that grows forever
• SQL Server log_reuse_wait_desc: why the transaction log will not truncate
• SQL Server transaction log percent used climbing toward full
• Microsoft SQL Server monitoring checklist: the signals every production instance needs
• Microsoft SQL Server monitoring maturity model: from survival to expert
• SQL Server runnable tasks backlog: the in-engine CPU queue OS metrics miss