{"id":3725,"date":"2012-07-09T15:31:20","date_gmt":"2012-07-09T15:31:20","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/its-time-that-you-ought-to-know-what-you-dont-know\/"},"modified":"2016-07-28T10:51:03","modified_gmt":"2016-07-28T10:51:03","slug":"its-time-that-you-ought-to-know-what-you-dont-know","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/its-time-that-you-ought-to-know-what-you-dont-know\/","title":{"rendered":"It’s time that you ought to know what you don’t know"},"content":{"rendered":"

There is a famous quote about unknown unknowns and known knowns and so on but I’ll let you review <\/a>that if you are interested.<\/p>\n

What I am worried about is that there are things going on in your environment that you ought to know about, indeed you have asked to be told about but you are not getting the information.<\/p>\n

When you schedule a SQL Agent job you can set it to send an email to an inbox monitored by someone who needs to know and indeed can do something about it. However, what happens if the email process isnt successful?<\/p>\n

Check your servers with this:<\/p>\n

\nUSE [msdb] \nGO\n \/* This code selects the top 10 most recent SQLAgent jobs that failed to \ncomplete successfully and where the email notification failed too. \nJonathan Allen Jul 2012 *\/\nDECLARE @Date DATETIME \nSELECT  @Date = DATEADD(d, DATEDIFF(d, '19000101', GETDATE()) - 1, '19000101') \nSELECT TOP 10\n        [s].[name] ,\n        [sjh].[step_name] ,\n        [sjh].[sql_message_id] ,\n        [sjh].[sql_severity] ,\n        [sjh].[message] ,\n        [sjh].[run_date] ,\n        [sjh].[run_time] ,\n        [sjh].[run_duration] ,\n        [sjh].[operator_id_emailed] ,\n        [sjh].[operator_id_netsent] ,\n        [sjh].[operator_id_paged] ,\n        [sjh].[retries_attempted]\nFROM    [dbo].[sysjobhistory] AS sjh\nINNER JOIN [dbo].[sysjobs] AS s\n        ON [sjh].[job_id] = [s].[job_id]\nWHERE   EXISTS ( SELECT *\n                 FROM   [dbo].[sysjobs] AS s\n                 INNER JOIN [dbo].[sysjobhistory] AS s2\n                        ON [s].[job_id] = [s2].[job_id]\n                 WHERE  [sjh].[job_id] = [s2].[job_id]\n                        AND [s2].[message] LIKE '%failed to notify%'\n                        AND CONVERT(DATETIME, CONVERT(VARCHAR(15), [s2].[run_date])) >= @date\n                        AND [s2].[run_status] = 0 )\n        AND sjh.[run_status] = 0\n        AND sjh.[step_id] != 0\n        AND CONVERT(DATETIME, CONVERT(VARCHAR(15), [run_date])) >= @date\nORDER BY [sjh].[run_date] DESC ,\n        [sjh].[run_time] DESC \n go<\/pre>\n
 USE [msdb] \ngo \n\/* This code summarises details of SQLAgent jobs that failed to complete successfully \nand where the email notification failed too. \nJonathan Allen Jul 2012 *\/\nDECLARE @Date DATETIME\nSELECT  @Date = DATEADD(d, DATEDIFF(d, '19000101', GETDATE()) - 1, '19000101')\nSELECT  [s].name ,\n        [s2].[step_id] ,\n        CONVERT(DATETIME, CONVERT(VARCHAR(15), [s2].[run_date])) AS [rundate] ,\n        COUNT(*) AS [execution count]\nFROM    [dbo].[sysjobs] AS s\nINNER JOIN [dbo].[sysjobhistory] AS s2\n        ON [s].[job_id] = [s2].[job_id]\nWHERE   [s2].[message] LIKE '%failed to notify%'\n        AND CONVERT(DATETIME, CONVERT(VARCHAR(15), [s2].[run_date])) >= @date\n        AND [s2].[run_status] = 0\nGROUP BY name ,\n        [s2].[step_id] ,\n        [s2].[run_date]\nORDER BY [s2].[run_dateDESC]<\/pre>\n
These two result sets will show if there are any SQL Agent jobs that have run on your servers that failed and failed to successfully email about the failure. I hope it’s of use to you.<\/p>\n
\"FriendOfRedGate_PLUS-LOGO_RGB_10pct.jpg\"<\/a>Disclaimer – Jonathan is a Friend of Red Gate and as such, whenever they are discussed, will have a generally positive disposition towards Red Gate tools. Other tools are often available and you should always try others before you come back and buy the Red Gate ones. All code in this blog is provided “as is” and no guarantee, warranty or accuracy is applicable or inferred, run the code on a test server and be sure to understand it before you run it on a server that means a lot to you or your manager.<\/p>\n","protected":false},"excerpt":{"rendered":"
There is a famous quote about unknown unknowns and known knowns and so on but I’ll let you review that if you are interested. What I am worried about is that there are things going on in your environment that you ought to know about, indeed you have asked to be told about but you……<\/p>\n","protected":false},"author":101210,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-3725","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/3725","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/101210"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=3725"}],"version-history":[{"count":28,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/3725\/revisions"}],"predecessor-version":[{"id":39586,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/3725\/revisions\/39586"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=3725"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=3725"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=3725"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=3725"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}