A planned failover that should have taken two minutes took forty-seven, all of it spent in crash recovery, and the instance's only crime was a transaction log containing more than 30,000 virtual log files, the fossil record of a decade of ten-percent autogrowth. The log file's size was fine. Its layout was a junkyard, and recovery had to walk every single piece of it before the database could come online.
The transaction log is not a file so much as a ring of chunks, and those chunks, the virtual log files or VLFs, decide how painful your recoveries, backups, and failovers are. Here is how the layout got bad, how to read what the log is telling you, and how to fix it once instead of forever.
What is a VLF, exactly?
A virtual log file is one fixed-size chunk of the physical log file, allocated when the file is created or grown. The log manager treats the file as a ring of VLFs: transactions fill them in sequence, and once the records in a VLF are no longer needed, that chunk is truncated and reused. VLFs have no size setting of their own; their size is decided entirely by the growth event that created them, which is why growth history matters so much.
Since SQL Server 2016 SP2 you can see the layout directly with sys.dm_db_log_info: every VLF with its vlf_begin_offset, vlf_size_mb, vlf_sequence_number, and vlf_status, where a status of 2 marks the active part of the ring. On older builds the same information comes from DBCC LOGINFO. The count is the health metric:
SELECT COUNT(*) AS vlf_count,
SUM(CASE WHEN vlf_status = 2 THEN 1 ELSE 0 END) AS active_vlfs,
MIN(vlf_size_mb) AS smallest_vlf_mb,
MAX(vlf_size_mb) AS largest_vlf_mb
FROM sys.dm_db_log_info(DB_ID(N'mydb'));How did my log end up with tens of thousands of VLFs?
Autogrowth wrote your layout. Every growth event appends a small batch of new VLFs, and years of tiny growth events, one MB here, ten percent there, stack up into tens of thousands of small chunks. Nobody chose this on purpose; the defaults chose it, one growth at a time, usually before anyone on the current team worked there.
The algorithm is worth memorizing, and it changed in 2014. The old rule was pure thresholds: a growth up to 64 MB created 4 VLFs, up to 1 GB created 8, and anything bigger created 16, with no ratio test at all, which is exactly how a log that grew one MB at a time into a two-hundred-GB file ended up as a VLF junkyard. Since SQL Server 2014 there is a smarter branch in front: if the growth is less than one-eighth of the current log size, the engine creates a single VLF that covers it, and only larger growths fall back to the old 4/8/16 thresholds. SQL Server 2022 and Azure SQL simplify it again, creating one VLF for any growth of 64 MB or less. When you inherit a server, you inherit its fossil record.
What does a VLF flood actually slow down?
Crash recovery first, because the analysis and redo passes walk the VLF chain, then log backups for the same reason, and then everything that scans the log: transactional replication, CDC capture, and AG log send can all degrade. The pain scales with VLF count, not with file size, which is the part that surprises people.
My 30,000-VLF instance needed forty-seven minutes of recovery for a log holding barely twenty GB of active records. After the cleanup described below, the same restart took two minutes. If your RTO math assumes recovery is fast, verify the assumption, because a VLF flood silently invalidates it and you find out during a failover, which is the worst possible time to learn arithmetic.
Why won't my log truncate, and what is log_reuse_wait_desc?
log_reuse_wait_desc in sys.databases is the oracle that names the single reason the tail of the log cannot be released right now, and each value has a different fix. Reading it takes one second; shrinking without reading it just makes the file bounce back to its old size with its old layout:
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE database_id > 4;The values you will actually meet:
- NOTHING: fine, truncation is already possible. If the file is still huge, that is a layout question, not a truncation question.
- LOG_BACKUP: truncation is waiting for a log backup; take one and the reusable part of the ring frees up. In FULL recovery this is the normal resting state between backups, recurring as new log outgrows the last truncation, not a sign that backups have never run.
- ACTIVE_TRANSACTION: an open transaction pins the oldest active VLF. Find it with DBCC OPENTRAN or sys.dm_tran_session_transactions joined to sys.dm_exec_sessions, and expect a forgotten SSMS window.
- AVAILABILITY_REPLICA: a secondary has not hardened, so the primary must keep the log. That is an AG problem wearing a log costume.
- REPLICATION or LOG_SCAN: the log reader job has not caught up. CHECKPOINT: SIMPLE recovery waiting for its checkpoint.
One honesty note on recovery models. In FULL, only a log backup frees VLFs. Switching to SIMPLE to force a truncation works, but it breaks the log backup chain, and you owe a full backup immediately after switching back before point-in-time restore means anything again. Teams that flip recovery models casually are trading a disk problem for a recoverability problem.
How do I rebuild the layout once, properly?
Truncate the log, shrink the file to near its floor, then regrow it in one deliberate step sized so the growth yields a small number of large VLFs, and set autogrowth to a fixed amount, never a percent. The part the folklore skips: DBCC SHRINKFILE can only release space behind the last active VLF, and log backups alone do not move it, because backups mark VLFs reusable while only new log generation advances the ring. The worked example from my 30,000-VLF instance: a log backup to truncate, enough workload to push the active VLF off the tail and back toward the front of the file, a second log backup to truncate the new tail, a stepped shrink down to a few hundred MB, then one regrowth to the 8 GB target, which lands as 16 VLFs of just under 500 MB each:
-- in FULL recovery: truncate, advance the ring off the tail, truncate again
BACKUP LOG mydb TO DISK = 'D:ackupsmydb_log_1.trn';
-- run workload (or wait) until sys.dm_db_log_info shows the active VLF
-- back near the front of the file
BACKUP LOG mydb TO DISK = 'D:ackupsmydb_log_2.trn';
DBCC SHRINKFILE (N'mydb_log', 256);
ALTER DATABASE mydb MODIFY FILE (NAME = mydb_log, SIZE = 8192MB);
ALTER DATABASE mydb MODIFY FILE (NAME = mydb_log, FILEGROWTH = 512MB);Verify afterwards with the count query from sys.dm_db_log_info, and if the shrink stalls short of the floor, the active VLF is still parked near the end of the file, so generate more log, truncate again, and retry. The healthy shape is a few hundred VLFs at tens to hundreds of MB each. Do not chase zero or some mythical perfect number: three hundred well-sized VLFs beat forty thousand tiny ones every day of the week, and nobody has ever been paged for three hundred VLFs. Do this once, during a maintenance window, document the final size, and the layout stays sane for the life of the database.
Watching the log ring with MonPG when SQL Server support ships
Log health is a trend, not an event: VLF count and active-VLF count per database, how long a database sits in ACTIVE_TRANSACTION or LOG_BACKUP before someone notices, and autogrowth events flagged as they happen, because each one is a layout decision made by a default. MonPG monitors PostgreSQL in production today, where the equivalent WAL discipline lives; SQL Server support is in active development, and these log counters are on the list of what it should graph from day one. Current status is on the SQL Server monitoring (coming soon) page, and until it lands, the two queries above in a scheduled job will catch every story in this article before it becomes a failover story.