SQL Server VLF Count Report: Virtual Log File Analysis

calendar May 18, 2026 / · 7 min read · sql server virtual log files dbcc loginfo transaction log database maintenance sql scripts database administration log management performance sys.databases  ·
Share on: linkedin copy

Excessive virtual log files (VLFs) inside the transaction log slow down database recovery, backup, and restore operations — often by minutes on large logs — and the problem compounds silently every time autogrowth fires with a small increment. This script queries DBCC LOGINFO across every online database on the instance, counts VLFs and active VLFs per database, and assigns a health assessment so DBAs can prioritize remediation.

Purpose and Overview

A SQL Server transaction log is internally divided into virtual log files. SQL Server creates VLFs automatically when the log file grows, and the number of VLFs per growth event depends on the size of the growth increment — small increments create fewer but more numerous VLFs over time, while a single large pre-allocation creates just a handful. When a log accumulates hundreds or thousands of VLFs, SQL Server must scan every VLF header during recovery, log backup, and restore, adding overhead that scales linearly with VLF count. Paul S. Randal of SQLskills.com documented the authoritative research on VLF overhead and the recommended thresholds in his foundational VLF series.

This script, adapted from the ktaranov/sqlserver-kit community repository, uses a cursor to execute DBCC LOGINFO against each online database and aggregates the results into a single report. The output columns are:

  • DatabaseName — the database being reported
  • VLFCount — total number of VLFs in the transaction log
  • ActiveVLFs — VLFs with Status = 2 (currently in use by the log)
  • LogSizeMB — total physical log file size in megabytes
  • Assessment — Healthy / Monitor / Excessive based on VLF count thresholds

Code Breakdown

The script loops every online database with a cursor, inserts DBCC LOGINFO output into a temp table, aggregates per database, and returns a single ranked result set.

 1-- VLF Count Report per Database
 2-- Source: ktaranov/sqlserver-kit community repository
 3-- Requires: VIEW SERVER STATE permission
 4-- SQL Server 2012+
 5
 6SET NOCOUNT ON;
 7
 8IF OBJECT_ID('tempdb..#VLFInfo') IS NOT NULL
 9    DROP TABLE #VLFInfo;
10
11CREATE TABLE #VLFInfo (
12    RecoveryUnitId  INT,
13    FileId          TINYINT,
14    FileSize        BIGINT,
15    StartOffset     BIGINT,
16    FSeqNo          BIGINT,
17    [Status]        TINYINT,
18    Parity          TINYINT,
19    CreateLSN       NUMERIC(25, 0)
20);
21
22DECLARE @DBName   NVARCHAR(128);
23DECLARE @SQL      NVARCHAR(512);
24DECLARE @Results  TABLE (
25    DatabaseName NVARCHAR(128),
26    VLFCount     INT,
27    ActiveVLFs   INT,
28    LogSizeMB    DECIMAL(10,2)
29);
30
31DECLARE db_cursor CURSOR FAST_FORWARD FOR
32    SELECT name
33    FROM sys.databases
34    WHERE state_desc = 'ONLINE'
35    ORDER BY name;
36
37OPEN db_cursor;
38FETCH NEXT FROM db_cursor INTO @DBName;
39
40WHILE @@FETCH_STATUS = 0
41BEGIN
42    TRUNCATE TABLE #VLFInfo;
43
44    SET @SQL = N'DBCC LOGINFO(' + QUOTENAME(@DBName, '''') + N') WITH NO_INFOMSGS;';
45
46    INSERT INTO #VLFInfo
47    EXEC sp_executesql @SQL;
48
49    INSERT INTO @Results (DatabaseName, VLFCount, ActiveVLFs, LogSizeMB)
50    SELECT
51        @DBName,
52        COUNT(*),
53        SUM(CASE WHEN [Status] = 2 THEN 1 ELSE 0 END),
54        CAST(SUM(FileSize) / 1048576.0 AS DECIMAL(10,2))
55    FROM #VLFInfo;
56
57    FETCH NEXT FROM db_cursor INTO @DBName;
58END;
59
60CLOSE db_cursor;
61DEALLOCATE db_cursor;
62
63DROP TABLE #VLFInfo;
64
65SELECT
66    DatabaseName,
67    VLFCount,
68    ActiveVLFs,
69    LogSizeMB,
70    CASE
71        WHEN VLFCount < 50   THEN 'Healthy'
72        WHEN VLFCount < 200  THEN 'Monitor'
73        ELSE                      'Excessive — Shrink and Resize Log'
74    END AS Assessment
75FROM @Results
76ORDER BY VLFCount DESC;

Temp Table and Cursor Setup

DBCC LOGINFO returns a tabular result set rather than a table-valued function, so the output cannot be directly inserted with a simple SELECT. The temp table #VLFInfo captures the DBCC output with one row per VLF. The cursor iterates sys.databases filtered to state_desc = 'ONLINE', skipping offline, restoring, or suspect databases that would cause DBCC LOGINFO to fail. After each iteration the temp table is truncated with TRUNCATE TABLE rather than deleted and recreated, which is faster on large servers.

DBCC LOGINFO Execution

DBCC LOGINFO returns one row per VLF with these key columns: FileId (the log file number for databases with multiple log files), FileSize (size of this VLF in bytes), StartOffset (byte position within the log file), FSeqNo (log sequence number order), Status (0 = inactive, 2 = active), and CreateLSN (the LSN at which this VLF was created). The Status = 2 filter in the aggregation identifies VLFs that SQL Server is currently writing to or has not yet truncated — these cannot be shrunk away. The file size is stored in bytes; dividing the sum by 1,048,576 (1 MB = 1,048,576 bytes) converts to megabytes.

Assessment Column

The three-tier assessment uses community-standard thresholds documented by Paul Randal: fewer than 50 VLFs is healthy for most databases; 50–199 is worth monitoring, particularly for databases with frequent log backups; 200 or more is excessive and warrants remediation. The thresholds are conservative — a very large database with a multi-gigabyte log may legitimately have more VLFs if the log file itself is large and the VLFs are correspondingly large. Use VLFCount in conjunction with LogSizeMB to judge: 200 VLFs on a 50 GB log is less concerning than 200 VLFs on a 500 MB log.

Key Benefits and Use Cases

  • Instance-wide visibility — reports all online databases in one result set, ranked by VLF count so the worst offenders surface immediately
  • Active VLF tracking — ActiveVLFs shows how many VLFs cannot be reclaimed, guiding log shrink feasibility
  • Assessment column — removes manual interpretation; Excessive databases can be filtered directly for remediation scripts
  • Pre-maintenance baseline — run before and after a log resize to confirm VLF count dropped as expected
  • Recovery time estimation — VLF count correlates directly with recovery duration; tracking it quarterly prevents silent degradation
  • Autogrowth diagnosis — a high VLF count relative to a small LogSizeMB reveals a history of many small autogrowth events

Performance Considerations

  • DBCC LOGINFO is metadata-only — it reads VLF header information from the log file without performing any I/O on data pages; each invocation is fast even on large log files
  • Cursor overhead scales with database count — on an instance with 200+ databases the cursor loop adds noticeable runtime; consider filtering the cursor to databases of interest rather than all online databases
  • VIEW SERVER STATE required — the executing login needs this server-level permission; db_owner on individual databases is not sufficient for cross-database execution
  • sys.dm_db_log_info alternative on SQL 2016+ — Microsoft introduced sys.dm_db_log_info as a table-valued function in SQL Server 2016; it returns the same VLF data without requiring DBCC and supports cross-database queries via sys.dm_db_log_info(database_id); for SQL 2016 and later, prefer the DMV over DBCC LOGINFO
  • Offline and restoring databases are excluded — the state_desc = 'ONLINE' filter silently skips AG secondaries in restoring state, mirroring partners, and databases in emergency mode; add a second result set or a separate query for those if needed

Practical Tips

  • Fix excessive VLFs in two steps: first shrink the log file to near zero (DBCC SHRINKFILE with TRUNCATEONLY after a log backup, or with a size argument), then immediately grow it back to the target size in a single operation using ALTER DATABASE … MODIFY FILE (SIZE = …). One large growth event creates just 8–16 VLFs regardless of target size; many small autogrowth events accumulate hundreds.
  • Pre-size logs at provisioning time — setting the log file to its anticipated steady-state size before the database goes into production eliminates almost all VLF fragmentation from the start.
  • Set autogrowth to fixed MB increments — percent-based autogrowth produces unpredictably sized growth events that create variable VLF counts. Use 256 MB or 1 GB fixed increments depending on database size.
  • Schedule this script monthly — add it as a SQL Agent job that writes results to a reporting table; alert when any database crosses 200 VLFs.
  • SQL 2016+ migration path — replace DBCC LOGINFO with SELECT DB_NAME(database_id), COUNT(*) FROM sys.dm_db_log_info(NULL) GROUP BY database_id for the current database, or join sys.databases with a CROSS APPLY to sys.dm_db_log_info(d.database_id) for all databases.

Conclusion

This script from the ktaranov/sqlserver-kit community repository provides a fast instance-wide VLF audit based on Paul S. Randal's foundational research into transaction log internals. Run it quarterly as part of routine index and log maintenance to catch databases where autogrowth has silently accumulated excessive VLFs, and use the two-step shrink-then-resize approach to bring VLF counts back to healthy levels before recovery or backup performance degrades.

References

Posts in this series