Does A Job Really Fail If Nobody Knows About It?

Have you ever gone out and made a new SQL Agent job and forget to assign notification settings for it? God, I hope I’m not the only one because that would mean I suck.

This is a common issue that exists in most environments that I’ve worked in. So I must not suck. Quite often I see SQL Agent Jobs executing throughout an organization and no one knows that a critical job has been failing day after day because the notifications where not setup for the job.

Every environment has its own requirements for how notifications need to be aggregated and handled. Most often the SQL Agent jobs are configured to send e-mail notifications and then the person that receives the e-mail is charged with resolving the issue. Sometimes these e-mails are sent to applications that automatically generate a ticket that is then forwarded to the responsible individual. In other environments, the failed jobs are logged to the event log and a server monitoring tool collects the event log information and generates alerts based on these entries.

The alert below was created with these two scenarios in mind. The procedure looks for jobs that don’t have either e-mail operators or an event log status set. To reduce clutter, it also ignores disabled jobs and jobs with the ‘Report Server’ category. The ‘Report Server’ category was mainly added to reduce noise from subscriptions created by reporting services.

The procedure accepts two parameters:

• @ToAddress: the person, group, or mailbox that needs to receive the alert.
• @SubjectWarning: a customizable message that can be added to the subject line of the e-mail alert. This was added so that when it is setup on pre-production and production servers the same alert code can be used on both servers but the subject line can include text informing the severity of the issue.

Typically this procedure gets scheduled once a day in the morning so that any items it finds can be resolved before the day gets going.

IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = 'Alert')
EXEC('CREATE SCHEMA [Alert] AUTHORIZATION [dbo]')
GO

IF EXISTS(SELECT * FROM sys.procedures WHERE name = 'SQLAgentJobsWithoutNotify')
DROP PROCEDURE [Alert].[SQLAgentJobsWithoutNotify]
GO

/*============================================================
Procedure: [Alert].[SQLAgentJobsWithoutNotify]
Author: Jason Strate
Date: 8-11-2004

Synopsis:

Exec [Alert].[SQLAgentJobsWithoutNotifyEmail]
@ToAddress = 'jstrate@hotmail.com'
,@SubjectWarning = 'TEST ALERT'

============================================================
Revision History:
Date: By Description
------------------------------------------------------------

============================================================*/
CREATE PROCEDURE [Alert].[SQLAgentJobsWithoutNotify]
(
@ToAddress varchar(255)
,@SubjectWarning varchar(255) = ''
)
AS

IF EXISTS (SELECT *
FROM msdb.dbo.sysjobs sj
LEFT OUTER JOIN msdb.dbo.syscategories c ON sj.category_id = c.category_id
WHERE sj.enabled = 1
AND sj.notify_email_operator_id = 0
AND sj.notify_level_eventlog = 0
AND c.[Name]  'Report Server')
BEGIN
DECLARE @Subject nvarchar(500),
@Body nvarchar(500),
@SQL nvarchar(max)

SELECT @Subject = @SubjectWarning + SPACE(1) + @@SERVERNAME + ': MSDB Job Notification Audit',
@Body = 'The following SQL Agent jobs do not have either e-mail or event log notifications configured to alert in the event that the job fails.',
@SQL = 'SET NOCOUNT ON;
SELECT CONVERT(varchar, COALESCE(sos.originating_server, @@SERVERNAME)) as [Originating Server],
CONVERT(varchar, sj.[name]) as [Job Name],
COALESCE(STUFF(STUFF(next_run_date, 7, 0, ''/''), 5, 0, ''/'') + Space(1) +
STUFF(STUFF(next_run_time, 5, 0, '':''), 3, 0, '':''), ''Unscheduled'') as [Next Run Date],
CONVERT(varchar, c.[name]) as [Category Name]
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb..sysjobschedules sjs ON sj.job_id = sjs.job_id
LEFT OUTER JOIN msdb.dbo.syscategories c ON sj.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysoriginatingservers sos ON sj.originating_server_id = sos.originating_server_id
WHERE sj.enabled = 1
AND sj.notify_email_operator_id = 0
AND sj.notify_level_eventlog = 0
AND c.[Name]  ''Report Server'''

EXEC msdb.dbo.sp_send_dbmail
@recipients = @ToAddress,
@subject = @Subject,
@body = @Body,
@execute_query_database = 'DBADiagnostics',
@query = @SQL,
@query_result_separator = '|'
END
GO