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 replic...