The Log File Won’t Shrink. Why?
The transaction log is one of the most misunderstood parts of SQL Server.
It grows. DBAs try to shrink it. It grows again.
The real question is never “why is the log big” — it’s why can’t SQL Server reuse the space inside it.
The log_reuse_wait_desc column answers that directly.
And then there are VLFs. Virtual Log Files are the internal segments inside the transaction log. When a log has been grown in tiny increments (especially with percent-based autogrowth), it accumulates thousands of VLFs. High VLF counts slow down crash recovery, log backup, and database attach.
These queries surface both problems.
What This Query Does
Part 1 — Log Usage and Reuse Wait
Joins sys.databases with sys.dm_os_performance_counters to show per-database:
- Allocated log size vs how much is actually used
Log Reuse Wait— the exact reason SQL Server can’t truncate the log yet
Common Log Reuse Wait values:
| Value | Meaning |
|---|---|
LOG_BACKUP | Waiting for a log backup — is your log backup job running? |
ACTIVE_TRANSACTION | A long-running transaction is holding the log open |
REPLICATION | Replication reader hasn’t consumed the log |
DATABASE_MIRRORING | Mirror is falling behind |
NOTHING | Log is free to be reused |
Part 2 — VLF Counts
Uses sys.dm_db_log_info (SQL Server 2016+) to count VLFs per database.
Over 500 is a warning. Over 1000 is worth fixing — shrink the log to near zero and regrow it in one large chunk.
Log File Usage and VLF Script
Loading…
Gareth Winterman