Quick Scan Report – High VLF Count

What this check looks for

DBCC LOGINFO run against every database, counting the rows returned. Each row is one virtual log file. Databases with more than 250 are reported.

The check is wrapped in a TRY block, because DBCC LOGINFO fails on a database that is not readable and one such database should not stop the sweep.

Why it matters

A transaction log is not one continuous space. Internally it is divided into virtual log files, and several operations have to walk every one of them.

The number is determined by how the log grew. SQL Server creates a set of VLFs inside each growth increment, and the count in each depends on the size of the growth:

So a log that reached 50 GB in 1 MB steps has tens of thousands of tiny VLFs. A log that reached the same size in 512 MB steps has a few hundred. Same size, same content, two very different files.

What walking them costs:

• Database startup and recovery. Recovery reads the log, and thousands of VLFs make it slow. On an instance with several affected databases, this is why a restart takes far longer than anybody expects.
• Restores. The same work, at the worst possible moment.
• Log backups, which get slower.
• Log truncation, which happens at VLF boundaries and behaves oddly when there are very many very small ones.

The startup case is the one that bites. Everything works normally day to day, and then a patching restart that should take two minutes takes forty, because recovery is walking a quarter of a million VLFs. That is a maintenance window overrun caused by a setting nobody looked at.

The cause is always the same: the log grew in small increments, either from a small fixed growth setting or from a percentage growth on a small file. Both have their own checks. A shrink and regrow cycle, from autoshrink or a scheduled shrink, produces it fastest of all.

How to confirm it yourself

On SQL Server 2016 SP2 and later, which is the clean way:

SELECT COUNT(*) AS [vlf_count]
  FROM sys.dm_db_log_info(DB_ID());

Across every database:

CREATE TABLE #vlf (DatabaseName SYSNAME, VLFCount INT);

EXEC sp_MSforeachdb N'
    USE [?];
    INSERT INTO #vlf (DatabaseName, VLFCount)
    SELECT DB_NAME(), COUNT(*) FROM sys.dm_db_log_info(DB_ID());';

SELECT * FROM #vlf ORDER BY VLFCount DESC;
DROP TABLE #vlf;

On older versions, DBCC LOGINFO returns one row per VLF:

DBCC LOGINFO;

And the cause, which is the growth setting:

SELECT DB_NAME([database_id])                                  AS [database_name],
       [name]                                                   AS [logical_name],
       CAST([size] * 8.0 / 1024 AS DECIMAL(12,1))               AS [size_mb],
       CASE WHEN [is_percent_growth] = 1 THEN CAST([growth] AS VARCHAR(10)) + ' %'
            ELSE CAST([growth] * 8 / 1024 AS VARCHAR(10)) + ' MB' END AS [growth]
  FROM sys.master_files WITH (NOLOCK)
 WHERE [type] = 1
 ORDER BY [size] DESC;

A few hundred VLFs is normal. A few thousand is worth fixing. Tens of thousands is why your restarts are slow.

How to fix it

Shrink the log right down, then grow it back in large steps. This is the one legitimate use of shrinking a log file, and it is done once rather than on a schedule.

-- 1. back up the log so the space becomes reusable (full recovery only)
BACKUP LOG [YourDatabase] TO DISK = N'\\backupserver\sqlbackups\YourDatabase_log.trn'
  WITH CHECKSUM, COMPRESSION;

-- 2. shrink it as far as it will go
USE [YourDatabase];
GO
DBCC SHRINKFILE (N'YourDatabase_log', 1);

-- 3. grow it back in large steps, to the size it actually needs
ALTER DATABASE [YourDatabase] MODIFY FILE (NAME = N'YourDatabase_log', SIZE = 8GB);

-- 4. and set a sensible growth increment so it does not happen again
ALTER DATABASE [YourDatabase] MODIFY FILE (NAME = N'YourDatabase_log', FILEGROWTH = 512MB);

Two practical notes:

• The shrink may not go all the way in one pass. The log can only release space up to the active portion, so if the active VLF is near the end you may need to back up the log again and repeat the shrink. Two or three rounds is normal.
• Grow it in one or a few large steps, not many small ones. Growing 8 GB in one statement gives you 16 VLFs. Growing it in 16 steps of 512 MB gives you 128. Both are fine; growing it in 1 MB steps is what got you here.

Size it for what it needs. The right size is whatever the log reaches under normal operation, including the largest index rebuild and the longest transaction. Shrinking it below that just means it grows again.

Do this when the database is quiet. It takes no downtime and the shrink competes with normal activity, so it is easier and faster on a quiet server. The finding’s own recommendation says the same thing.

Then fix the settings that caused it, or it comes back: the growth increment, any percentage growth, and autoshrink if it is on. All three have their own checks.

How long it takes

About two hours per affected database, most of it the shrink and regrow. No downtime required.

Related reports

Related checks

Frequently asked questions

How many VLFs is too many? The check reports above 250. A few hundred is unremarkable, a few thousand is worth fixing, and tens of thousands explains slow startups on its own.

Is shrinking the log not always bad? For a data file, essentially always. For a log file being rebuilt deliberately to fix VLF fragmentation, it is the correct tool, used once, followed by a planned regrow.

Do I need downtime? No. It is easier on a quiet server because the shrink competes with activity, but the database stays online.

Why did it get so bad? A small or percentage growth increment, a shrink and regrow cycle, or both. The growth query above shows which.