As a minimum every SQL Agent Job should have an operator (or DBA) notification setup ideally for failure or completion.
How do I check which operators are setup against my Agent Jobs I hear you say!
Well run this query below to find out. If you have any agent jobs return as ‘No email notification configured’ in the [Notify_level_email] field then an email notification is not setup and you should look into these as a priority (unless you have other methods of monitoring Agent Jobs that is!)
SELECT SJ.NAME, SO.[name],SO.[email_address],
[notify_level_email] =
CASE notify_level_email
WHEN '1' THEN 'When the job succeeds'
WHEN '2' THEN 'When the job fails'
WHEN '3' THEN 'When the job completes'
ELSE 'No email notification configured'
END
FROM
msdb.dbo.sysjobs SJ LEFT JOIN [msdb].[dbo].[sysoperators] SO
ON SJ.notify_email_operator_id = SO.ID
ORDER BY SO.ID, SJ.NAME
Download Finding-which-Operators-are-setup-against-SQL-Agent-Job-Notifications (294 downloads)
The example below shows the operators setup against Agent Jobs and the email address the notification will be sent to, the first row shows that no operator is setup on this job for email notifications, if this job fails no-one may be aware.
There’s a few pre-requisites that your need to ensure are setup first for Agent Email Notifications to work:
1. Database Mail should be configured and working (see this great article on SQL Server Central if you don’t know how to setup Database Mail)
2. Agent Operators should be setup with valid email addresses (see my post on recommended operators to setup)
3. Agent Jobs should have an email notification configured ideally for ‘When jobs the fails’
