{"id":208,"date":"2012-07-27T17:30:57","date_gmt":"2012-07-27T16:30:57","guid":{"rendered":"http:\/\/www.darrencoupland.com\/blog\/?p=208"},"modified":"2012-08-14T14:38:14","modified_gmt":"2012-08-14T13:38:14","slug":"finding-which-operators-are-setup-against-sql-agent-job-notifications","status":"publish","type":"post","link":"https:\/\/www.darrencoupland.com\/blog\/2012\/07\/finding-which-operators-are-setup-against-sql-agent-job-notifications\/","title":{"rendered":"Finding which Operators are setup against SQL Agent Job Notifications"},"content":{"rendered":"

As a minimum every SQL Agent Job should have an operator (or DBA) notification setup ideally for failure or completion.<\/p>\n

\"Agent<\/a><\/p>\n

How do I check which operators are setup against my Agent Jobs I hear you say!<\/p>\n

Well run this query below to find out. \u00a0If you have any agent jobs return as ‘No email notification configured’ in the\u00a0[Notify_level_email] field\u00a0then 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!)<\/p>\n

SELECT SJ.NAME, SO.[name],SO.[email_address],\r\n[notify_level_email] =\r\nCASE notify_level_email\r\n     WHEN '1' THEN 'When the job succeeds'\r\n     WHEN '2' THEN 'When the job fails'\r\n     WHEN '3' THEN 'When the job completes'\r\n     ELSE 'No email notification configured'\r\nEND\r\nFROM\r\nmsdb.dbo.sysjobs SJ LEFT JOIN [msdb].[dbo].[sysoperators] SO \r\nON SJ.notify_email_operator_id = SO.ID\r\nORDER BY SO.ID, SJ.NAME<\/pre>\n
Download\u00a0\n\tFinding-which-Operators-are-setup-against-SQL-Agent-Job-Notifications\t(5813 downloads\t)\n<\/a>\n<\/p>\n
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.<\/p>\n
\"Agent<\/p>\n
 <\/p>\n
There’s a few pre-requisites that your need to ensure are setup first for Agent Email Notifications to work:
\n1. \u00a0Database Mail should be configured and working (see this great article<\/a> on SQL Server Central if you don’t know how to setup Database Mail)
\n2. \u00a0Agent Operators should be setup with valid email addresses (see my post<\/a> on recommended operators to setup) \u00a0<\/span><\/strong>
\n3. \u00a0Agent Jobs should have an email notification configured ideally for ‘When jobs the fails’<\/p>\n","protected":false},"excerpt":{"rendered":"
As a minimum every SQL Agent Job should have an operator (or DBA) notification setup ideally for failure or completion.<\/p>\n
How do I check which operators are setup against my Agent Jobs I hear you say!
\nRun the query in this post to find out.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[5],"tags":[29,16,17,60,28],"class_list":["post-208","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-administration","tag-sql-agent","tag-sql-agent-jobs","tag-sql-server","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/posts\/208","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/comments?post=208"}],"version-history":[{"count":0,"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/posts\/208\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/media?parent=208"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/categories?post=208"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/tags?post=208"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}