info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Replication, Cloud & Migrations



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









T-SQL:


Previous Method:

DBCC LOGINFO;


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: