Monitor SQL Server Transaction Log Backups Across Always On Replicas

This post applies to SQL Server 2016 and later, where newer DMVs make it easier to inspect transaction log activity across databases and replicas.

For years I relied on the backup history tables in msdb to check whether backups were running on schedule and staying within SLAs. There are, of course, other ways to monitor backups, but I prefer having a simple, query‑driven view that I can trust.

I don’t like taking chances with database backups, not just for disaster recovery but also for day‑to‑day availability. If transaction log backups are skipped due to oversight or repeated job failures, the log can grow until it fills the underlying disk, potentially taking not only that database offline but any others sharing the same volume.

To avoid that, I use a script that checks when the last successful transaction log backup completed and compares it to a threshold (for example, more than 24 hours). If that threshold is exceeded, I get an automatic email alert.

Things get even more interesting once you add Availability Groups into the mix, especially if your organization uses third-party backup tools. SQL Server allows transaction log backups to run on any eligible replica rather than forcing them onto the primary. That flexibility is great for offloading work, but it also means you need to know which replica can back up which database types.

Here is a quick summary of which backup types are allowed on each replica type.

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 recorded only on the replica where the backup actually occurs, and that data doesn't automatically sync across replicas.

That makes checking for the last successful backup more complicated than it first appears.

If you always run backups from one specific replica, you can usually rely on msdb's backup history tables. But even then, you'll eventually face some unpleasant surprises (are there any other kind?).

To cover all the bases, I needed a more reliable approach. Luckily, Microsoft introduced the sys.dm_db_log_stats function in SQL Server 2016 and later. This DMV has fields that get updated regardless of whether you're on the primary or secondary replica, including log_backup_time, which shows the timestamp of the last successful log backup.

Here's the query I use to monitor transaction log backups across all replicas.

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);

It gives you a reliable, replica-agnostic view of transaction log backup health across your SQL Server environment. Drop it into a scheduled job with your 24-hour threshold, pipe the results to an email alert. Simple, effective monitoring that catches problems before they become outages.