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:
|
|
|
|
|
|