Users: 30k; Pages Per Session: 2.5p; Avg. Session Duration: 1:35mi
Explore Tools
{Featuring Microsoft Technologies + Enterprise Data + Cloud Computing}

SQL Server 2017 - sys.dm_db_log_info
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)


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: