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 are not getting the information.

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?

Check your servers with this:

USE [msdb] 
GO
 /* This code selects the top 10 most recent SQLAgent jobs that failed to 
complete successfully and where the email notification failed too. 
Jonathan Allen Jul 2012 */
DECLARE @Date DATETIME 
SELECT  @Date = DATEADD(d, DATEDIFF(d, '19000101', GETDATE()) - 1, '19000101') 
SELECT TOP 10
        [s].[name] ,
        [sjh].[step_name] ,
        [sjh].[sql_message_id] ,
        [sjh].[sql_severity] ,
        [sjh].[message] ,
        [sjh].[run_date] ,
        [sjh].[run_time] ,
        [sjh].[run_duration] ,
        [sjh].[operator_id_emailed] ,
        [sjh].[operator_id_netsent] ,
        [sjh].[operator_id_paged] ,
        [sjh].[retries_attempted]
FROM    [dbo].[sysjobhistory] AS sjh
INNER JOIN [dbo].[sysjobs] AS s
        ON [sjh].[job_id] = [s].[job_id]
WHERE   EXISTS ( SELECT *
                 FROM   [dbo].[sysjobs] AS s
                 INNER JOIN [dbo].[sysjobhistory] AS s2
                        ON [s].[job_id] = [s2].[job_id]
                 WHERE  [sjh].[job_id] = [s2].[job_id]
                        AND [s2].[message] LIKE '%failed to notify%'
                        AND CONVERT(DATETIME, CONVERT(VARCHAR(15), [s2].[run_date])) >= @date
                        AND [s2].[run_status] = 0 )
        AND sjh.[run_status] = 0
        AND sjh.[step_id] != 0
        AND CONVERT(DATETIME, CONVERT(VARCHAR(15), [run_date])) >= @date
ORDER BY [sjh].[run_date] DESC ,
        [sjh].[run_time] DESC 
 go

 USE [msdb] 
go 
/* This code summarises details of SQLAgent jobs that failed to complete successfully 
and where the email notification failed too. 
Jonathan Allen Jul 2012 */
DECLARE @Date DATETIME
SELECT  @Date = DATEADD(d, DATEDIFF(d, '19000101', GETDATE()) - 1, '19000101')
SELECT  [s].name ,
        [s2].[step_id] ,
        CONVERT(DATETIME, CONVERT(VARCHAR(15), [s2].[run_date])) AS [rundate] ,
        COUNT(*) AS [execution count]
FROM    [dbo].[sysjobs] AS s
INNER JOIN [dbo].[sysjobhistory] AS s2
        ON [s].[job_id] = [s2].[job_id]
WHERE   [s2].[message] LIKE '%failed to notify%'
        AND CONVERT(DATETIME, CONVERT(VARCHAR(15), [s2].[run_date])) >= @date
        AND [s2].[run_status] = 0
GROUP BY name ,
        [s2].[step_id] ,
        [s2].[run_date]
ORDER BY [s2].[run_dateDESC]

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.

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.