sys.dm_db_log_stats–New DMF in SQL Server 2017 to keep track of your Log backup and Log changes


SQL Server 2017 introduces a new Dynamic Management Function “sys.dm_db_log_stats” to track information on log files, the most interesting information it provides is when was the last log backup completed and size of the log growth since last backup

This will be very helpful for high transaction system when log backup has to be done on regular basis, you can create your monitoring jobs to track using functions whether log backup is running or not using “log_backup_time

Other option we can design the log backup job is to run it based on specific size of growth since last backup using “log_since_last_log_backup_mb” instead of running it on regular schedule, that will reduce the number of log backups as well on times when there are less or no changes

Sample Usage

DECLARE @DBID INT = Db_id()

SELECT
       database_id,
       recovery_model,
       total_log_size_mb,
       active_log_size_mb,
       log_truncation_holdup_reason,
       log_backup_time,
       log_since_last_log_backup_mb
FROM   sys.Dm_db_log_stats(@DBID)

Output

capture20171018120433316

Hope you find this post helpful !!!

Author: Arunraj

I am a Microsoft Certified Technology Specialist (Database Developer). I work on SQL Server programming since SQL Server 7.0 specializes in SQL Server Programming and Performance Tuning and has 14 years of hands-on experience. I hold a Master Degree in Computer Applications. I am also part of NJSQL User Group and Northern New Jersey .Net User Group.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s