Alerts
Alert 1105
Large VLF Count
Alert 1105 is a transaction log file alert and is triggered when dbOmnibus detects a transaction log file contains a large number of Virtial Log Files (VLF).
It is recommended to shrink the log file.
If this occurs aften, consider increasing the log growth size.
(As a general rule of thumb, a healthy database should ideally maintain fewer than a few hundred VLFs, rather than several thousand.)
Consequences of a large VLF count
A high Virtual Log File (VLF) count in a SQL Server transaction log can severely degrade database performance, slow down recovery times, and introduce operational bottlenecks. When a transaction log fills up, SQL Server divides it into smaller internal chunks called VLFs. If your transaction log auto-grew too many times in small increments (e.g., setting growth to 10MB or 50MB instead of a proper sizing strategy), you can end up with thousands or tens of thousands of VLFs.
1. Slow Database Startup & Crash Recovery (REDO / UNDO)
This is typically the most dangerous impact of a high VLF count.
- The Mechanism: During database startup, a failover, an unexpected crash, or a clean shutdown, SQL Server must run recovery. It reads the transaction log to roll forward uncommitted transactions (REDO) and roll back uncompleted ones (UNDO).
- The Consequence: SQL Server evaluates VLFs sequentially during this phase. If you have 50,000 VLFs instead of 50, recovery has to inspect metadata and markers across tens of thousands of individual file segments. This can dramatically extend recovery time, leaving your database offline or in "Recovery Pending" state for an unacceptably long period. In a high-availability setup like Always On Availability Groups, this can cause automated failovers to fail or timeout.
2. Backup and Restore Delays
- The Consequence: Taking full, differential, or transaction log backups requires SQL Server to scan and process log file headers and allocation maps. A massive VLF metadata footprint creates internal overhead during backup initialization and completion, increasing backup duration and CPU consumption. Restoring databases with high VLF counts similarly suffers from extended processing overhead.
3. Increased CPU and Latch Contention
- The Mechanism: The SQL Server storage engine manages the transaction log via internal synchronization mechanisms, including latches and mutexes, to track VLF status (active vs. inactive/reusable).
- The Consequence: When hundreds of concurrent transactions are committing, SQL Server constantly has to allocate, clear, and traverse a massive list of VLFs. This creates internal latch contention on log structures (LOGFLUSH or VLF allocation latches), driving up CPU utilization without contributing to actual application work.
4. Slower Transaction Commits & DML Performance
- The Consequence: While each individual write is buffered, the overhead of managing thousands of tiny log segments can add microsecond-to-millisecond delays to transaction log flushes (COMMIT TRAN). Under high-throughput OLTP workloads, this cumulative serialization bottleneck reduces overall transaction throughput (TPS).
Page Last Updated: 26 October 2026