SQL Server Health Check

How High Virtual Log File (VLF) Count Kills SQL Performance

Updated August 2, 20263 min read

Too many VLFs slow down SQL Server. Ploom stands beside a huge pile of tiny thin booklets, next to one thick book.

What is a virtual log file?

The transaction log file is physically divided internally into several virtual log files.

This number can grow based on how often the active transactions are written to the disk and the auto-growth settings for the log file.

With high SQL Server VLF counts, backups will run slower. Multiple SQL operations will be slowed down.

Some T-SQL operations, such as UPDATE/DELETE will take longer.

SQL Server Database Engine service will take longer to start.

Replication/AlwaysOn/Mirroring/Log shipping and other operations – all suffer.

How to identify the issue?

You can get information about the VLF count using DBCC LOGINFO.

For newer SQL Server versions (SQL Server 2016 SP2 and later), you may use the query below that uses SQL Dynamic Management Functions (DMFs).

T-SQL
SELECT [name] AS 'Database Name'
	,COUNT(l.database_id) AS 'VLF Count'
	,SUM(CAST(vlf_active AS INT)) AS 'Active VLF'
	,COUNT(l.database_id) - SUM(CAST(vlf_active AS INT)) AS 'Inactive VLF'
	,SUM(vlf_size_mb) AS 'VLF Size (MB)'
	,SUM(vlf_active * vlf_size_mb) AS 'Active VLF Size (MB)'
	,SUM(vlf_size_mb) - SUM(vlf_active * vlf_size_mb) AS 'Inactive VLF Size (MB)'
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY [name]
ORDER BY COUNT(l.database_id)
Figure 1 – Query SQL Server VLFs output
Figure 1 – Query VLFs output

When VLF under 100 – you can ignore.

When between 100 – 200 – you can ignore, but better to fix.

When above 400 – it’s getting urgent, so fix it.

When above 600 – slowdowns are happening, but it’s not easy to diagnose
these. Fix.

When above 5000, fix now!

How to fix it?

  1. Fix database default growth settings.
  2. Shrink transaction log files, and pre-grow to set sizes.

A high VLF count usually means growth settings drifted for months with nobody watching. Red9's SQL Server managed support keeps log files, growth settings, and backups tuned as part of routine monthly maintenance.

More information

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜