Get VLF count for all databases

Returns virtual log file (VLF) information of the transaction log.

Query determines the databases with more than xx VLFs in the log files, which can affect the database startup, restore, and recovery time.

SELECT [name] AS [Database Name]
	,[VLF Count]
FROM sys.databases AS db WITH (NOLOCK)
CROSS APPLY (
	SELECT file_id
		,COUNT(*) AS [VLF Count]
	FROM sys.dm_db_log_info(db.database_id)
	GROUP BY file_id
	) AS li
ORDER BY [VLF Count] DESC
OPTION (RECOMPILE);

select * from sys.dm_db_log_info(db_id())

dm_db_log_info