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.

VLF countImpactAction
Under 1,000Manageable; recovery normalBaseline and trend
1,000 to 10,000Recovery and log backups visibly slowerPlan consolidation
Above 10,000Recovery will be significantly slowRemediate before the next restart or failover

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

Common causes

CauseWhat it looks likeFirst thing to check
Small autogrowth incrementsLog file has grown hundreds of times in small stepssys.dm_db_log_info count plus default trace autogrowth history
Growth setting never re-tunedLog grew steadily over time with the original growth settingFile growth settings via sys.master_files
Long-lived database, never remediatedOlder production database that never had VLF maintenanceVLF count per database across the whole instance
Shrink and regrow cycleLog grew large, was shrunk, then grew backError log and default trace for shrink and growth pairs

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 DATABASE STATE in the target database.
<!-- TODO: verify exact minimum permission for sys.dm_db_log_info across versions -->
--   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 / 1000 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;
<!-- TODO: verify IntegerData semantics and EventClass 93 across SQL Server versions -->

-- 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

SignalWhy it mattersWarning sign
VLF count per databaseDirect measure of fragmentationSustained climb above 1,000, or already above 10,000
Log autogrowth eventsEach growth event adds VLFsFrequent small autogrowth entries in default trace
Last RECOVERING durationReal-world impact of VLF countRecovery time growing over successive restarts
log_reuse_wait_descDetermines whether shrink can reclaim VLFsAnything other than NOTHING blocks VLF reclamation
AG redo queue sizeVLF-heavy databases slow secondary redoRedo queue growing on a high-VLF database with healthy secondary resources
Log backup durationEach backup scans VLF headersBackup time rising without proportional log size growth

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.

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.
  • 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.
  • 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.