VLF (Virtual Log Files) Count in SQL Server

By /

What is VLF (Virtual Log Files) in SQL Server?

VLF (Virtual Log Files) are smaller chunks inside the SQL Server transaction log file (LDF). SQL Server divides the transaction log file into multiple VLFs to manage transactions efficiently.

However, if there are too many VLFs, it can slow down:

  • Database startup
  • Log backup and restore operations
  • Transaction log growth performance
How to Check VLF Count in SQL Server?

Use the DBCC LOGINFO or sys.dm_db_log_info command to check VLFs in a database.

Method 1: Using DBCC LOGINFO (Older Versions)

DBCC LOGINFO;

Method 2: Using sys.dm_db_log_info (SQL Server 2016+)

SELECT COUNT(*) AS VLF_Count FROM sys.dm_db_log_info(DB_ID());

What is an Optimal VLF Count?

Good VLF Count:

< 100 VLFs → Healthy
100-200 VLFs → Acceptable

Bad VLF Count:

500+ VLFs → Performance issue
Thousands of VLFs → Very slow log backups & recovery

How to Reduce High VLF Count?

If your VLF count is too high, follow these steps:

Step 1: Shrink the Log File

DBCC SHRINKFILE ('YourDatabase_log', TRUNCATEONLY);

Step 2: Resize the Log File with Proper Growth Settings

  • Set Initial Size Properly (Avoid small increments)
ALTER DATABASE YourDatabase 
MODIFY FILE (NAME = YourDatabase_log, SIZE = 512MB);
  • Set Growth in MB, Not Percentage
ALTER DATABASE YourDatabase MODIFY FILE (NAME = YourDatabase_log, FILEGROWTH = 128MB);

Avoid small growth values (e.g., 1MB, 10MB). Recommended: 128MB to 1GB based on workload.

Step 3: Recycle the Log File

If VLFs are still too many:

ALTER DATABASE YourDatabase SET RECOVERY SIMPLE;
DBCC SHRINKFILE ('YourDatabase_log', 1);
ALTER DATABASE YourDatabase SET RECOVERY FULL;

This shrinks the log completely and removes excessive VLFs.

Best Practices for Managing VLFs
Best PracticeWhy?
Set proper initial log sizePrevent excessive VLF creation
Avoid percentage-based growthLeads to fragmentation and too many VLFs
Check VLF count regularlyUse sys.dm_db_log_info(DB_ID())
Shrink log file only when necessaryFrequent shrinking can cause fragmentation
Summary
  • Too many VLFs slow down database performance.
  • Check VLF count using sys.dm_db_log_info(DB_ID()).
  • Fix high VLFs by properly sizing and managing log growth.

Scroll to Top