Although I have previously tackled this problem by rolling my own notifications. This time I realised that the SQL jobs that run every minute or two are of low importance – and I don’t really want emails when they fail. I will notice at some point during the working day.
Here is the code to lists jobs that run frequently and are configured to send notification emails. Along with the commands to remove those notifications.
/* JobNotificationEmailStop.sql */
SELECT S.[name] JobName,
SS.freq_subday_interval [ScheduleFreq(mins)],
'EXEC msdb.dbo.sp_update_job @job_name = N'''
+ S.[name]
+ ''', @notify_email_operator_name = N'''';'
CommandToDisableEmailNotification
FROM msdb.dbo.sysjobs S
JOIN msdb.dbo.sysjobschedules SJ
ON S.job_id = SJ.job_id
JOIN msdb.dbo.sysschedules SS
ON SS.schedule_id = SJ.schedule_id
WHERE SS.freq_subday_interval > 0
AND S.notify_level_email > 0
ORDER BY SS.freq_subday_interval;
Database Consultant 