SQL Server VLF Management Best Practices
This guide focus on the most misunderstood aspects of SQL Server performance: Virtual Log Files (VLFs). Whether you are a seasoned DBA or a developer wondering why your database is taking "forever" to recover after a crash, this essay answers the What, Why, and How of VLF management with best practices.
1. The Basics: What is a VLF?
To understand a Virtual Log File (VLF), we first have to look at the Transaction Log (.LDF file). Unlike your data files (.MDF), which are organized into 8KB pages and 64KB extents, the transaction log is a serial record of every change made to the database.
However, SQL Server doesn't treat the log as one giant, continuous stream. Internally, it breaks the physical log file into smaller chunks called Virtual Log Files.
Why the "Virtual" name?
They are "virtual" because you don't define their size or number directly when you create a database.
2. The Architecture: How VLFs are Born
When you create a database or grow your log file, SQL Server uses a specific algorithm to determine the number of VLFs. This is where most people get into trouble.
The Standard Algorithm (SQL Server 2014 through 2026)
The number of VLFs created during a growth event depends on the size of the growth:
| Growth Amount | Number of VLFs Created |
| Less than 64 MB | 4 VLFs |
| 64 MB to 1 GB | 8 VLFs |
| Greater than 1 GB | 16 VLFs |
The "Death by a Thousand Cuts" Scenario: > Imagine you have a 100 GB log file, but your autogrowth is set to the default 1 MB. To get to 100 GB, your log has to grow 100,000 times. Each 1 MB growth creates 4 VLFs.
You now have 400,000 VLFs. This is what DBAs call "Log Fragmentation."
3. The "Why": Why Do VLFs Matter?
You might be thinking, "So what if I have thousands of small files inside my log? SQL is fast!" Unfortunately, the SQL Server engine has to manage these VLFs linearly.
A. Slow Database Recovery (RTO)
When SQL Server starts up, or when a database is restored, it must go through a process called Recovery. It reads the transaction log to ensure the data is consistent (rolling back uncommitted transactions and rolling forward committed ones).
If you have 100 VLFs, SQL Server scans them quickly.
If you have 50,000 VLFs, SQL Server has to open, read, and manage each one. This can turn a 2-minute recovery into a 2-hour nightmare.
B. Sluggish Backups and Restores
Transaction log backups have to "crawl" through the VLF chain. High VLF counts add significant overhead to every log backup, increasing your backup window and putting unnecessary strain on the CPU.
C. AlwaysOn and Replication Latency
In modern environments using AlwaysOn Availability Groups, the "Log Send" and "Redo" processes are constant. If the primary has high VLF counts, the secondary replicas often struggle to keep up because the management overhead of switching between thousands of VLFs slows down the data stream.
D. Modification Slowness
While INSERT, UPDATE, and DELETE operations are fast, the moment the log needs to wrap around (reuse space) or grow, a high VLF count causes a "hiccup." The engine spends more time managing the metadata of the log than actually writing your data.
4. The Symptoms: How to Check Your VLF Health
In 2026, we have better tools than the old DBCC LOGINFO. While that still works, the modern way is using Dynamic Management Views (DMVs).
The "New" Script (SQL Server 2017+)
Run this to see exactly how many VLFs you have and which databases are in the "Danger Zone":
SELECT
[name] AS 'Database Name',
COUNT(l.database_id) AS 'VLF Count',
SUM(CAST(vlf_size_mb AS DECIMAL(10,2))) AS 'Total Log Size (MB)',
MAX(vlf_size_mb) AS 'Max VLF Size (MB)',
MIN(vlf_size_mb) AS 'Min VLF Size (MB)'
FROM sys.databases d
CROSS APPLY sys.dm_db_log_info(d.database_id) l
GROUP BY [name]
ORDER BY [VLF Count] DESC;
What is a "Good" Number?
Under 100: Perfect.
200 – 500: Acceptable, but keep an eye on it.
500 – 1,000: Sub-optimal. You’ll start seeing slow recovery.
1,000+: You have a "High VLF Count" problem.
10,000+: This is a production emergency. Fix it during your next maintenance window.
5. The Evolution: SQL Server 2022 & 2025 Improvements
Microsoft realized that DBAs aren't always great at setting autogrowth. In SQL Server 2022 and the recently released SQL Server 2025, the engine became "smarter."
Reduced Fragmentation: The engine now tries to cap the number of VLFs created during large growth events to prevent the "16 VLF" rule from creating too many small chunks when growing massive logs.
Log Growth Speed: Improvements in how the OS zero-fills log files mean that even large growths (which used to hang the system) are now faster, encouraging DBAs to use larger growth increments (like 1 GB or 8 GB).
6. The Solution: How to Resolve High VLF Counts
If you found a database with 5,000 VLFs, don't panic. You can fix it without taking the database offline, though it involves some "log gymnastics."
Step 1: Clear the Log
You cannot shrink a log that is "full" of active transactions.
If in SIMPLE recovery: Run
CHECKPOINT.If in FULL recovery: Run a
BACKUP LOG.
Step 2: Shrink the Log
We want to shrink the log file to its smallest possible size to "wipe the slate clean."
USE [YourDatabaseName];
GO
-- Shrink the log file to 0 MB (it will shrink to the smallest possible VLF size)
DBCC SHRINKFILE (N'YourLogFileName_Log', 0, TRUNCATEONLY);
GO
Step 3: Pre-Grow the Log (The Most Important Step)
Do not let it grow back naturally via autogrowth. Manually grow it to your target size (e.g., 8 GB) in chunks.
Rule of thumb: Grow in 4 GB or 8 GB increments to ensure you get 16 healthy, large VLFs.
ALTER DATABASE [YourDatabaseName]
MODIFY FILE ( NAME = N'YourLogFileName_Log', SIZE = 8192MB );
7. Best Practices: Proactive Management
1. Set a Fixed Autogrowth
Forget percentages. Never use "Growth by 10%." On a 1 TB log, a 10% growth is 100 GB—too big! On a 10 MB log, it's 1 MB—too small (High VLFs!).
Recommendation: Set autogrowth to 1024 MB (1 GB) or 4096 MB (4 GB) for most production databases.
2. Right-Size from Day One
If you know your database log usually stays around 50 GB, create the database with a 50 GB log immediately. Don't start at 10 MB and let it struggle to get to 50 GB.
3. Frequent Log Backups
VLFs only become "inactive" and reusable after a log backup (in Full recovery). If you only backup your log once a day, the log will grow massive and create thousands of VLFs. Backup your log every 15 minutes (or less) to keep the VLF count stable.
4. Monitor with Automation
Use a tool (or a SQL Agent Job) to alert you if any database exceeds 1,000 VLFs. Catching it early means a 5-second fix instead of a 5-hour headache.
Summary Checklist
| Action Item | Best Practice Value |
| Max VLF Count | Keep under 1,000 (Target 50-200) |
| Autogrowth Setting | Fixed size (e.g., 1024 MB), never % |
| Initial Log Size | 20-30% of total database size |
| Recovery Model | Match your RPO; use Log Backups to clear VLFs |
| Verification Tool | sys.dm_db_log_info |
Managing VLFs isn't just about "tidying up"—it's about ensuring that when your server goes down at 3 AM, it comes back up in seconds, not hours. By right-sizing your logs and setting sane growth increments, you're building a more resilient, high-performance SQL Server environment.
