Notify if no log backups occured in the last n minutes for a given database (in AG)

notify-no-log-backups.sql

DECLARE	@OperatorName	sysname = N'OperatorName';
DECLARE	@MailProfile	sysname = N'MailProfile'
DECLARE	@DatabaseName	sysname = N'DatabaseName';
DECLARE	@MaxMinutesGap	int = 4 * 60;

DECLARE	@MinutesGap	int = (
	SELECT	DATEDIFF(minute, MAX(backup_finish_date), GETDATE())
	FROM	msdb.dbo.backupset AS B1
	JOIN	msdb.dbo.backupmediaset AS B2
	ON		B1.media_set_id = B2.media_set_id
	JOIN	msdb.dbo.backupmediafamily AS B3
	ON		B1.media_set_id = B3.media_set_id
	WHERE	B1.[database_name] = @DatabaseName
		AND	B1.[type] = 'L'
		AND	B1.is_copy_only = 0
		AND	sys.fn_hadr_backup_is_preferred_replica(@DatabaseName) = 1
)
PRINT @MinutesGap
IF @MinutesGap >= @MaxMinutesGap
BEGIN
	DECLARE @MailBody nvarchar(max) = FORMATMESSAGE('Log backup for database %s not running since %u minutes on server %s', @DatabaseName, @MinutesGap, @@SERVERNAME);
	DECLARE	@EmailToNotify	nvarchar(max) = (SELECT email_address FROM msdb.dbo.sysoperators WHERE [name] = @OperatorName);
	EXEC sp_send_dbmail @profile_name = @MailProfile, @recipients = @EmailToNotify, @subject = 'Log backup not running', @body = @MailBody, @importance = 'High';
	RAISERROR (@MailBody, 11, 1);
END