Daily Failed Jobs Report Emailed

Since I posted about jobs that run too long (Stop Jobs That Run Too Long – SQLRx), I thought I would also post about a way to have a daily failed jobs report sent to whomever needs it.

One issue that seems to be prevalent on many servers is that jobs get set up and then often no one monitors if they are running successfully.  Since I usually get to monitor servers that already have jobs and lots of business logic going on that I have no idea what it’s all about, I generally have to ask around to see if anyone cares if “X” job is failing and try to help them with the errors after that.  However, if I have a manager that does care that jobs are running successfully, I can generally give them either a list of failed jobs or a summary of number of times jobs have failed that can be used to get developers to take a look at things. 

I created a stored procedure for this and have the code set up to create a temp table of the failed jobs in the last 24 hours and then if it finds more than 20 records, it changes from creating a list to creating a summary table of the number of times each job failed.  You will need to provide the mail profile name and a semicolon delimited list of email recipients.

USE DBA
GO
CREATE PROCEDURE DailyFailedJobsReport
	@MailProfile VARCHAR(128),
	@EmailRecipients VARCHAR(200)
AS
SET NOCOUNT ON 

DECLARE @cnt INT
DECLARE @body1 VARCHAR(2000)
DECLARE @InstName VARCHAR(128)
DECLARE @subj VARCHAR(100)
DECLARE @summary BIT

SELECT @InstName = CONVERT(VARCHAR(128), SERVERPROPERTY('ServerName'))

CREATE TABLE #failed_jobs ( 
ServerName VARCHAR(100), 
JobName VARCHAR(100), 
JobStepName VARCHAR(100), 
JobLastRun DATETIME, 
JobRunStatus VARCHAR(10), 
ErrorMessage VARCHAR(500)
)  

SET @summary = 0  -- start off assuming to create a list of failed jobs

-- find jobs that failed
INSERT INTO #failed_jobs (ServerName, JobName, JobStepName, JobLastRun, JobRunStatus, ErrorMessage)
SELECT h.server, j.[name], h.step_name,
	   msdb.dbo.agent_datetime(h.run_date,h.run_time) as [Job Last Run],
	   'FAILED', h.message
FROM   msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id AND h.step_id = s.step_id
WHERE  h.run_status = 0 -- Failed 
AND msdb.dbo.agent_datetime(h.run_date,h.run_time) > DATEADD(dd, -1, GETDATE())

SELECT @cnt = count(*) FROM #failed_jobs

IF @cnt > 20  -- if there are a lot of job failures switch to sending a summary
	SET @summary = 1

IF @cnt > 0 and @summary = 0
BEGIN
	SET @subj = 'List of Failed Jobs on '+@InstName+' in the last 24 hours'
	SET @body1='<table border="2" cellspacing="2" cellpadding="2"> 
	  <TR><td colspan=2 
	  style="color: #A0522D; font-family: Arial; font-size: 12;" align=left>'
	-- table title
	SET @body1=@body1+'Failed Jobs</TD></TR>'
	-- column headers
	SET @body1=@body1+ '<tbody align="left" style="font-family:Arial; font-size: 11;" <TR><TH>ServerName</TH><TH>JobName</TH><TH>JobStepName</TH><TH>JobLastRun</TH>
		<TH>JobRunStatus</TH><TH>ErrorMessage</TH></TR></tbody>'
	-- data
	SELECT @body1=@body1 +'<TR><TD>'+ServerName+'</TD><TD>'+JobName+'</TD><TD>'+
		JobStepName+'</TD><TD>'+CAST(JobLastRun AS VARCHAR(30))+'</TD><TD>'+JobRunStatus+'</TD><TD>'+ErrorMessage+'</TD></TR>' 
	FROM #failed_jobs
	ORDER BY JobLastRun DESC
	-- Send an html formatted e-mail to notify of job failures
	EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @MailProfile,
    @recipients = @EmailRecipients,
    @subject = @subj, 
    @body = @body1,
    @body_format = 'HTML';
	--print @body1
END

IF @cnt > 0  and @summary = 1
BEGIN
	SET @subj = 'Summary of Failed Jobs on '+@InstName+' in the last 24 hours'
	SET @body1='<table border="2" cellspacing="2" cellpadding="2"> 
	  <TR><td colspan=2 
	  style="color: #A0522D; font-family: Arial; font-size: 12;" align=left>'
	-- table title
	SET @body1=@body1+'Failed Jobs</TD></TR>'
	-- column headers
	SET @body1=@body1+ '<tbody align="left" style="font-family:Arial; font-size: 11;" <TR><TH>Job Name</TH><TH>Num Times Fail In 24Hrs</TH></TR></tbody>'
	-- data
	SELECT @body1=@body1 +'<TR><TD>'+JobName+'</TD><TD>'+CAST(COUNT(*) AS VARCHAR(30))+'</TD></TR>' 
	FROM #failed_jobs
	GROUP BY JobName
	ORDER BY JobName
	-- Send an html formatted e-mail to notify of job failures 
	EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @MailProfile,
    @recipients = @EmailRecipients,
    @subject = @subj, 
    @body = @body1,
    @body_format = 'HTML';
	--print @body1
END

DROP TABLE #failed_jobs
SET NOCOUNT OFF 
GO

I didn’t get super fancy with the HTML formatting as this is meant to inform and not be flashy.  If you like to make things much prettier, have at it!  😊

Once you get the stored procedure installed, you can then call it in a job.

USE [msdb]
GO
/****** Object:  Job [Daily Failed Jobs Report]    ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Daily Failed Jobs Report', 
		@enabled=0, 
		@notify_level_eventlog=0, 
		@notify_level_email=2, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', 
		@job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [FailedJobsReport]    ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'FailedJobsReport', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXEC DailyFailedJobsReport @MailProfile = ''DBMail'', @EmailRecipients = ''DBA@company.com;AppManager@company.com''', 
		@database_name=N'DBA', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'DailyReportSchedule', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=1, 
		@active_start_date=20260101, 
		@active_end_date=99991231, 
		@active_start_time=20000, 
		@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

The output looks similar to this:

List

Summary

Hope this is helpful to someone!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!