| 437-991-3573 | Data Engineering Services
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance

SQL Server 2017 - sys.dm_db_log_info
by BF (Principal Consultant; Architecture; Engineering)


Previous Method:


New Method:

SELECT * FROM sys.dm_db_log_info(DB_ID(DB_NAME()))

Find the Total VLF's for each Database:

SELECT UPPER([Name]) as 'DB', Count(l.database_id) as 'Total VLFs'
FROM sys.databases s
Cross Apply sys.dm_db_log_info(s.database_id) l
Group By [name]

Find the status of the last VLF in a Database:

SELECT TOP 1 DB_NAME(database_id) as 'DB',File_id, vlf_size_mb, vlf_sequence_number, vlf_active, vlf_status
FROM sys.dm_db_log_info((DB_ID(DB_NAME())))
ORDER BY vlf_sequence_number DESC

It’s a very good idea to monitor, log & alert on the number of VLOGS in each database as it can lead to performance issues in some cases.

Positioned Resources: