Rolling my own Notifications – I wanted a sql-job that ran every 5 minutes to Notify me when it failed – but not to “spam” me every 5 minutes until I fixed it!
Here’s the simplified last-job-step that would email me the first time the job failed, but then NOT again for half an hour.
declare @email varchar(500), @operator varchar(50) set @operator = 'DBA' if (select datediff(mi, max(send_request_date), getdate()) from [msdb].[dbo].[sysmail_mailitems] where subject like 'ALERT%') > 30 begin set @email = 'execute msdb.dbo.sp_notify_operator @name=N''' + @operator + ''', @subject=N''ALERT: [XX] failed '', @body=N''The [XX] sql-job on server [YY] has failed!''' exec(@email) select 'email sent' end