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

Question: How to Get VLF Count and Size Information in SQL Server?

Answer: Well, if you are seeking an answer to this question in earlier versions of SQL Server, I have previously blogged about it here: SQL SERVER – Detect Virtual Log Files (VLF) in LDF, similarly I have blogged about how to reduce VLFs in this blog post: SQL SERVER – Reduce the Virtual Log Files (VLFs) from LDF file. Additionally, if you want to find out VLF details using PowerShell, you can read this blog post: SQL SERVER – PowerShell to Count Number of VLFs in SQL Server.

In this blog post, we will see how we can get information about the VLF count and its size with the newly introduced Dynamic Management Functions (DMF).

SELECT [name], s.database_id,
COUNT(l.database_id) AS 'VLF Count',
SUM(vlf_size_mb) AS 'VLF Size (MB)',
SUM(CAST(vlf_active AS INT)) AS 'Active VLF',
SUM(vlf_active*vlf_size_mb) AS 'Active VLF Size (MB)',
COUNT(l.database_id)-SUM(CAST(vlf_active AS INT)) AS 'In-active VLF',
SUM(vlf_size_mb)-SUM(vlf_active*vlf_size_mb) AS 'In-active VLF Size (MB)'
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY [name], s.database_id
ORDER BY 'VLF Count' DESC
GO

Above script will give us results with VLF count along with active and inactive VLF count as well. Additionally, it also gives us information about the size of Active and Inactive VLF count. This is indeed a very handy script but it will only work with the SQL Server 2017 and later version of SQL Server.

If you have started to use SQL Server 2017 or later version, you must start using this script instead of the earlier script as this script provides more relevant information.

Reference: Pinal Dave (https://blog.sqlauthority.com)