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    DATABASE_NAME,
    AG.NAME   AG_NAME,
    DBL.LOG_BACKUP_TIME,
    DATEDIFF(MINUTE, DBL.LOG_BACKUP_TIME, GETDATE()) MinutesSinceLastLogBackup,
    HDRS.IS_PRIMARY_REPLICA,
    DBL.RECOVERY_MODEL,
    DBL.LOG_SINCE_LAST_LOG_BACKUP_MB,
    D.STATE_DESC,
    D.IS_READ_ONLY,
    D.LOG_REUSE_WAIT_DESC,
    DATABASEPROPERTYEX(D.NAME, 'Updateability') DATABASE_MODE

FROM
    SYS.DATABASES D
    LEFT JOIN SYS.DM_HADR_DATABASE_REPLICA_STATES HDRS 

         ON HDRS.GROUP_DATABASE_ID = D.GROUP_DATABASE_ID
            AND HDRS.REPLICA_ID = D.REPLICA_ID
    LEFT JOIN SYS.AVAILABILITY_GROUPS AG ON AG.GROUP_ID = HDRS.GROUP_ID 
    OUTER APPLY SYS.DM_DB_LOG_STATS ( D.DATABASE_ID )    DBL

WHERE  1 = 1
   AND DBL.RECOVERY_MODEL != 'SIMPLE'
--   AND DATEDIFF(MINUTE, DBL.LOG_BACKUP_TIME, GETDATE()) > 60*24

ORDER BY  DATABASE_NAME

---