LLI Wiki — My Digital Knowledge Base

Detect the number of VLFs in a transaction log

Query that counts the virtual log files (VLFs) and active VLFs of each database, to spot an excessive VLF count.

Each transaction log is internally split into virtual log files (VLFs). An excessive number of VLFs can slow down log-related operations such as recovery and log backups. The query below counts the VLFs and the active VLFs of every database.

Query

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

Notes

References

Mastodon LLIMastodon SocialFosstodon