How to Get VLF Count and Size in SQL Server? – Interview Question of the Week #161

Question: How can I see the count, size and active state of the virtual log files (VLFs) in my SQL Server databases?

Answer: Read sys.dm_db_log_info and aggregate its VLF rows by database. The function is available in SQL Server 2016 SP2 and later, and it returns one row for each VLF, including its size and whether it is active. Before it existed, this took undocumented DBCC commands or a PowerShell script.

Here is an instance-wide view. Sort by the computed column, not by quoted words such as 'VLF Count'; a quoted name is a string literal, so it can’t put the databases in VLF-count order.

SELECT d.name AS DatabaseName,
       d.database_id AS DatabaseID,
       COUNT(*) AS VLFCount,
       SUM(li.vlf_size_mb) AS VLFSizeMB,
       SUM(CONVERT(int, li.vlf_active)) AS ActiveVLFCount,
       SUM(CASE WHEN li.vlf_active = 1 THEN li.vlf_size_mb ELSE 0 END)
           AS ActiveVLFSizeMB,
       SUM(CASE WHEN li.vlf_active = 0 THEN 1 ELSE 0 END)
           AS InactiveVLFCount,
       SUM(CASE WHEN li.vlf_active = 0 THEN li.vlf_size_mb ELSE 0 END)
           AS InactiveVLFSizeMB
FROM sys.databases AS d
CROSS APPLY sys.dm_db_log_info(d.database_id) AS li
WHERE d.state_desc = N'ONLINE'
GROUP BY d.name, d.database_id
ORDER BY VLFCount DESC, d.name;

This counts VLFs across every log file in each online database. The active and inactive sizes are sums of whole VLF sizes, not a measure of exactly how many bytes of log records are in use.

SQL Server 2022 and later require VIEW DATABASE PERFORMANCE STATE for the target database; older supported versions require VIEW SERVER STATE. An instance-wide query needs appropriate access to every database it examines. If your account can’t inspect one of them, run the function only for a database you’re authorized to examine.

A high count is a reason to look at the log’s growth history and current workload, not a reason to shrink immediately. Many small autogrowth steps are the usual cause, so a sensible fixed growth size fixes it at the source.

A high VLF count is not a reason to shrink, it is a record of how the log grew, so fix the growth setting.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.