SQL Server 2017 sys.dm_db_log_info

There is a new DMF called sys.dm_db_log_info which takes a single int parameter called @DatabaseId.
 
Below shows the output compared to the older DBCC LOGINFO and it makes vlf info a lot easier to review.

--old way
DBCC LOGINFO;

--SQL Server 2017
SELECT * FROM sys.dm_db_log_info(DB_ID(DB_NAME()));

image1
Below is an example of viewing multiple database vlf counts.

SELECT a.[name] AS [database_name], COUNT(b.database_id) AS vlfs 
FROM sys.databases a
CROSS APPLY sys.dm_db_log_info(a.database_id) b
GROUP BY a.[name];

image
Futher info can be found here.

Leave a Reply