Understanding Virtual Log Files (VLFs) in SQL Server: What They Are, Why They Matter, and How to Manage Them

---

Virtual Log Files are one of the less visible aspects of SQL Server internals, but excessive VLF counts are a real and common cause of slow recovery times, slow log backups, and overhead in Always On Availability Groups and replication. They are almost always caused by poor transaction log autogrowth settings that were never reviewed after initial setup.

This article explains what VLFs are, how they accumulate, what the consequences of too many look like, and the exact steps to prevent and fix the problem.

1 What a Virtual Log File Is Beginner

SQL Server transaction logs are stored in one or more .ldf files on disk. Internally, SQL Server does not use the log file as one continuous structure. Instead it divides the physical log file into segments called Virtual Log Files. VLFs are the smallest unit the log manager works with for recovery, truncation, and log space reuse.

A useful analogy: the physical .ldf file is a book, and VLFs are the chapters inside it. When SQL Server needs to track which transactions have been written, backed up, or need to be replayed during recovery, it works through these chapters in sequence. The number of chapters and their size affects how efficiently SQL Server can perform log-related operations.

VLF count and size are not directly configurable. SQL Server determines them automatically based on how the log file is created and how it grows over time.

2 How VLFs Are Created Beginner

VLFs are created in two situations: when the database is initially created and when the log file grows. The number of VLFs created by each growth event depends on the size of the growth increment according to Microsoft’s documented thresholds.

Growth Size	VLFs Added	Implication
Less than 64 MB	4 VLFs per growth event	Many small growth events create hundreds of tiny VLFs quickly
64 MB to 1 GB	8 VLFs per growth event	Moderate growth still accumulates VLFs if events are frequent
Greater than 1 GB	16 VLFs per growth event	Large growth chunks minimize VLF count

The root cause of excessive VLF counts is almost always a default autogrowth setting of 1 MB or a small percentage that was never changed from the SQL Server installation default. A log file that starts at 8 MB and grows in 1 MB increments, which creates 4 VLFs per growth event, can accumulate thousands of VLFs before anyone notices.

3 How Excessive VLFs Hurt Performance Intermediate

• Slow startup and crash recovery. During crash recovery SQL Server must scan each VLF to determine which transactions need to be replayed or rolled back. With thousands of tiny VLFs, this scan takes significantly longer. On a production server this translates directly to longer downtime after an unplanned restart.
• Slow log backups and restores. Each VLF is processed individually during log backup and restore operations. A log backup that should complete in seconds can take minutes on a database with tens of thousands of VLFs.
• Increased latency in Always On AG, log shipping, and replication. These features read continuously from the transaction log. Log reader agents must navigate through every active VLF. Excessive VLFs add overhead to each scan cycle.
• Log management overhead. SQL Server tracks the status of every VLF (active, inactive, reusable). With thousands of VLFs this status tracking adds background overhead that compounds under load.

4 How to Check VLF Count Beginner

sys.dm_db_log_info is the correct DMV for VLF analysis on SQL Server 2016 SP2 and later. For older versions, DBCC LOGINFO provides equivalent information but in a less structured format.

-- SQL Server 2016 SP2 and later: VLF count per database
SELECT
    d.name                              AS DatabaseName,
    COUNT(li.vlf_sequence_number)       AS VLFCount,
    SUM(li.vlf_size_mb)                 AS TotalLogMB,
    AVG(li.vlf_size_mb)                 AS AvgVLFSizeMB
FROM sys.databases                      d
CROSS APPLY sys.dm_db_log_info(d.database_id) li
WHERE d.database_id > 4                 -- exclude system databases
GROUP BY d.name
ORDER BY VLFCount DESC;

-- Detail: view individual VLF status for a specific database
SELECT
    vlf_sequence_number,
    vlf_active,                         -- 1 = active (cannot be truncated)
    vlf_status,                         -- 0 = available, 2 = active
    vlf_size_mb,
    vlf_begin_offset
FROM sys.dm_db_log_info(DB_ID('YourDatabaseName'))
ORDER BY vlf_sequence_number;

-- Legacy: pre-SQL 2016 SP2
DBCC LOGINFO;
-- Each row in the output is one VLF
-- Column 'Status': 0 = inactive (reusable), 2 = active

5 VLF Count Guidelines Beginner

VLF Count	Status	Action
Under 1,000	Generally fine	Monitor as part of regular health checks
1,000 to 10,000	Monitor closely	Review autogrowth settings, plan remediation if workload is growing
Over 10,000	Problematic	Fix as soon as a maintenance window is available

These are widely accepted community guidelines rather than hard Microsoft limits. The impact of a given VLF count depends on database size, workload, and how often log-related operations run. A database with 2,000 VLFs on a server with daily log backups and no Always On AG may show no noticeable symptoms. The same VLF count on a server with synchronous AG replicas may show measurable redo latency on the secondary.

6 Prevention: Correct Log File Sizing Beginner

The goal is to size the log file large enough that autogrowth events are rare, and to set the autogrowth increment large enough that when growth does occur it produces few VLFs.

-- Step 1: Pre-size the log file to cover normal workload
-- This avoids constant autogrowth events entirely
ALTER DATABASE YourDatabase
MODIFY FILE (NAME = YourDatabase_log, SIZE = 1GB);

-- Step 2: Set a sensible fixed autogrowth increment
-- 512 MB is a reasonable starting point for most production databases
-- Avoid percentage growth: 10% of a 500 GB log = 50 GB growth = 16 huge VLFs
ALTER DATABASE YourDatabase
MODIFY FILE (NAME = YourDatabase_log, FILEGROWTH = 512MB);

-- Verify the new settings
SELECT
    name,
    size * 8 / 1024         AS CurrentSizeMB,
    growth * 8 / 1024       AS GrowthMB,
    is_percent_growth
FROM sys.master_files
WHERE database_id = DB_ID('YourDatabase')
  AND type_desc = 'LOG';

7 Remediation: Fixing Existing VLF Bloat Intermediate

If a database already has thousands of VLFs, the remediation is to shrink the log file to a small size and then immediately regrow it in one large chunk. This consolidates the thousands of small VLFs into a small number of large ones. Schedule this during a maintenance window with minimal activity.

-- Step 1: Back up the transaction log first (FULL recovery model)
-- Skipping this loses the ability to restore to a point in time
BACKUP LOG YourDatabase
TO DISK = 'D:\Backups\YourDatabase_log_before_vlf_fix.trn';

-- Step 2: Shrink the log file to a small size
-- This removes inactive VLF space but does not truncate active VLFs
-- If the log will not shrink, active transactions are holding it open
DBCC SHRINKFILE (YourDatabase_log, 1);  -- shrink to approximately 1 MB

-- Step 3: Verify current size after shrink
SELECT name, size * 8 / 1024 AS CurrentSizeMB
FROM sys.database_files
WHERE type_desc = 'LOG';

-- Step 4: Regrow in one large chunk
-- This single growth event creates a maximum of 16 VLFs
ALTER DATABASE YourDatabase
MODIFY FILE (NAME = YourDatabase_log, SIZE = 4GB);

-- Step 5: Set sensible autogrowth to prevent recurrence
ALTER DATABASE YourDatabase
MODIFY FILE (NAME = YourDatabase_log, FILEGROWTH = 512MB);

-- Step 6: Verify VLF count improved
SELECT COUNT(*) AS VLFCount
FROM sys.dm_db_log_info(DB_ID('YourDatabase'));

8 Hands-On Lab: Create and Fix VLF Bloat Advanced

This lab creates VLF bloat deliberately in a test environment, confirms the problem with the DMV, then fixes it. Run only on a non-production SQL Server instance.

-- Step 1: Create a test database with bad autogrowth settings
-- 1 MB growth = 4 VLFs per growth event = classic bloat scenario
CREATE DATABASE VLFTest
ON PRIMARY (
    NAME = VLFTest_data,
    FILENAME = 'C:\SQLData\VLFTest_data.mdf',
    SIZE = 10MB
)
LOG ON (
    NAME = VLFTest_log,
    FILENAME = 'C:\SQLData\VLFTest_log.ldf',
    SIZE = 1MB,
    FILEGROWTH = 1MB  -- deliberately bad: 4 VLFs per growth event
);
GO

-- Step 2: Force many autogrowth events to accumulate VLFs
USE VLFTest;
GO

CREATE TABLE dbo.BigTable (ID INT IDENTITY, SomeData CHAR(8000) DEFAULT 'X');
SET NOCOUNT ON;

DECLARE @i INT = 0;
WHILE @i < 5000
BEGIN
    INSERT INTO dbo.BigTable DEFAULT VALUES;
    SET @i += 1;
END;
GO

-- Step 3: Check the VLF count after bloat
SELECT COUNT(*) AS VLFCount
FROM sys.dm_db_log_info(DB_ID('VLFTest'));
-- Expected: hundreds to thousands of VLFs

-- Step 4: Back up the log before shrinking
BACKUP LOG VLFTest TO DISK = 'C:\SQLData\VLFTest_before_fix.trn';

-- Step 5: Shrink the log
DBCC SHRINKFILE (VLFTest_log, 1);

-- Step 6: Regrow in one large chunk
ALTER DATABASE VLFTest
MODIFY FILE (NAME = VLFTest_log, SIZE = 512MB, FILEGROWTH = 128MB);

-- Step 7: Verify the improvement
SELECT COUNT(*) AS VLFCount
FROM sys.dm_db_log_info(DB_ID('VLFTest'));
-- Expected: a small number of large VLFs

-- Step 8: Clean up the lab database
USE master;
DROP DATABASE VLFTest;

References

• Microsoft Docs: Transaction Log Architecture and Management Guide
• Microsoft Docs: sys.dm_db_log_info
• SQLYARD: SQL Server Transaction Log Full: Every Cause, the Right Fix, and What Not to Do
• SQLYARD: SQL Server Instance Setup and Best Practices
• SQLYARD: SQL Server Health Check Toolkit