Can you please help me how to setup an alert about no log backup for a database in full recovery ?
This will provide a boolean value of 'True' or 'False' if the lastest TLog backup for specific
database is not withing the configured threshold. You would then configure the custom alert as a True/False
This only works for one database at a time.
A value of TRUE would mean the last LOG backup is older than your threshold
if you need to monitor MINUTES instead of HOURS, change the value in the DATEDIFF statement
DECLARE @Threshold int = 4 -- number of hours
DECLARE @DBName varchar(100) = 'AfsAudit_Iberia' --- name of database you want to monitor
select top 1
datediff(MINUTE, b.backup_finish_date, getdate())
,case when datediff(HOUR, b.backup_finish_date, getdate()) > @Threshold then '1' else '0' end [NoLog]
from dbo.backupset b
where b.type='L' and b.database_name=@DBName
order by b.backup_finish_date DESC
Thank you...very useful...need to look into custom alerts