Getting most recent transaction log backup time irrespective of where the transaction log backup is performed in always on availability groups

I should mention outright that this post applies to SQL Server version 2016 and up.

Over the years I have relied on the backup history tables in msdb to check if backups are performed regularly and/or if they are falling behind the SLAs. Of course there are other, maybe better ways to monitor your backups too.

But I don't take chances with database backups, not only for the DR purposes but also for the database availability reasons as well. If the transaction log back ups are getting skipped due to some oversight or failing for some reason, it may fill up the disks and lead to outage with not just that database but for other databases sharing the same disks.

So I have a script that checks when was the last successful transaction log backup performed and if it exceeds a threshold, for example more than 24 hours, I get an automatic email alert.

Things got little more interesting in high availability group clusters, SQL Server allows the log backup to be performed on any of the replicas. 

Here is summary of what types of backups are allowed on what type of replicas.

Backup Type	Primary Replica	Secondary Replica (Synchronous and Asynchronous)
Full	Yes	Yes but with Copy_Only option)
Log	Yes	Yes (we cannot take log backup with COPY_ONLY on secondary replica)
Differential	Yes	No

Backup events are only records in the replica where the backup is performed and those data do not get automatically synced across all replicas.

So it makes it bit more complicated to check when was the last successful backup.

Now if you happen to always run your backups on a particular replica then you are fine relying on the backup history tables for the most of the times. Even then eventually you will be faced with some very unpleasant surprises (are there any other kinds?).

So to cover all the bases, I needed to find another way. Luckily Microsoft introduced a new system function dm_db_log_stats in SQL Server 2016 and onward that has couple fields that get updated regardless its a primary replica or secondary and among them is log_backup_time field that indicates time of last successful backup. And based on that here is the query that I am using to monitor transaction log backups.

SELECT 
    d.name AS [Database],
    dbl.recovery_model,
    dbl.log_backup_time,
    dbl.total_log_size_mb,
    dbl.log_since_last_log_backup_mb,
    dbl.log_since_last_checkpoint_mb,
    dbl.total_vlf_count,
    dbl.active_vlf_count,
    dbl.active_log_size_mb,
    dbl.log_truncation_holdup_reason,
    dbl.log_state
FROM 
    sys.databases d
CROSS APPLY 
    sys.dm_db_log_stats(d.database_id) dbl;

Partial result:

Here is an example of one of its use cases:

--===============================================================================
-- TRANSACTION LOG BACKUP CHECK AND EXECUTION SCRIPT
-- Purpose: Automatically check database transaction log backup status and trigger
-- backup if older than 24 hours. Designed for automated maintenance jobs.
-- Author: DBA Team
-- Last Updated: Jan 16, 2026
--===============================================================================

DECLARE @DBName sysname = 'msdb';  -- Target database
DECLARE @DBID int = DB_ID(@DBName); -- Get database ID for DMV query efficiency

-- Validate database exists
IF @DBID IS NULL
BEGIN
    RAISERROR('ERROR: Database %s does not exist or DB_ID lookup failed.', 16, 1, @DBName);
    RETURN;
END;

-- Check last transaction log backup time using dynamic management view
DECLARE @LogBackupTime datetime = (
    SELECT log_backup_time 
    FROM sys.dm_db_log_stats(@DBID)  -- Returns last log backup timestamp
);


-- Main logic: Trigger backup if log backup is older than 24 hours
IF @LogBackupTime < DATEADD(DAY, -1, GETDATE())  -- Explicit 24hr threshold
BEGIN
    DECLARE @SQL NVARCHAR(4000);
    SET @SQL = N'BACKUP LOG ' + QUOTENAME(@DBName) + 
               N' TO DISK = N''C:\' + @DBName + N'.TRN''' +
               N' WITH INIT, COMPRESSION';
    
    PRINT 'Executing: ' + @SQL;
    
    -- TODO: Uncomment for production execution
    -- EXEC sp_executesql @SQL;
    
END
ELSE
BEGIN
    PRINT 'Log backup current: ' + CONVERT(varchar(23), @LogBackupTime, 121);
END;

-- Cleanup and return status
PRINT 'Log backup check completed for ' + @DBName + ' at ' + CONVERT(varchar(23), GETDATE(), 121);