How To Access SQL Server Transaction Log File Information with New Dmw sys.dm_db_log_stats in SQL Server 2017

dbtut

4 years ago

New Dmw sys.dm_db_log_stats in SQL Server 2017

To understand SQL Server architecture, you must understand the architecture of the SQL Server Transaction Log file. Transaction Log file is also one of the most important components in terms of performance. To understand the architecture of the Transaction Log file, I recommend reading “What is SQL Server Transaction Log”

With SQL Server 2017, a dmv has been announced to query the transaction log file. We could access transaction log informations in other ways instead of using this dmw. But in my opinion, such a dmw was needed for easier access.

Using this dmw, we can easily find databases with high vlf count or databases without log backups.

I recommend you to read the article “Vlf (Virtual Log File) Count” to learn detailed information about Vlf. As an important note, vlf count is very important for database performance.

Find VLF Count by using sys.dm_db_log_stats

Below you can find the query that shows the vlf count of databases if vlf count of database is more than 100.

SELECT name AS 'DBName', total_vlf_count AS 'VLF Count' 
FROM sys.databases AS db
CROSS APPLY sys.dm_db_log_stats(db.database_id) 
WHERE total_vlf_count  > 100;

SELECT name AS 'DBName', total_vlf_count AS 'VLF Count'

FROM sys.databases AS db

CROSS APPLY sys.dm_db_log_stats(db.database_id)

WHERE total_vlf_count > 100;

Find Last Log Backup Date by using sys.dm_db_log_stats

Below you can find the query that shows the last log backup date of the databases.

SELECT name AS 'DBName', log_backup_time AS 'LastLogBackupDate' 
FROM sys.databases AS db
CROSS APPLY sys.dm_db_log_stats(db.database_id);

SELECT name AS 'DBName', log_backup_time AS 'LastLogBackupDate'

FROM sys.databases AS db

CROSS APPLY sys.dm_db_log_stats(db.database_id);