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
Hope you find this post helpful !!!
SQLXpertise 