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

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 (

, , , ,
Previous Post
How to Schedule a Job in SQL Server? – Interview Question of the Week #160
Next Post
How to Reduce High Virtual Log File (VLF) Count? – Interview Question of the Week #162

Related Posts

2 Comments. Leave new

  • Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘sys.dm_db_log_info’.

  • Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘sys.dm_db_log_info’.

    i am facing the same issue
    i am using version 2014

    how can i identify VLF counts and size in 2014 version


Leave a Reply Cancel reply

Exit mobile version