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
sys.dm_db_log_infois available only in recent SQL Server versions. On older versions,DBCC LOGINFOreturns the VLF list of one database.ORDER BY 'VLF Count'sorts on a constant string, so the result is not sorted. UseORDER BY [VLF Count] DESCto sort by VLF count.- The source covers both detecting and reducing VLFs. The reduction method is described in the references below.