Summarize Your SQL Job Failures with an Automated Script

** Managing failed SQL Server jobs can be a time-consuming and error-prone task. Automating the monitoring and notification process can save valuable time and ensure that failures are addressed promptly. Below, we’ll explore the benefits and drawbacks of streamlining this process and provide you with a powerful script to get started.

The Script

Use this script to automate the monitoring of failed SQL Server jobs and receive daily email notifications summarizing the failures. If you have critical jobs you will still want to alert on those and get those to a SQL DBA to meet the business continuity needed. The following script is designed to help you with the jobs that are less critical that can be handled during business hours.

USE [msdb]
GO

/****** Object: Job [_DBA JobFailure Summary Last 24 hours] Script Date: 2/12/2024 2:49:51 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 2/12/2024 2:49:51 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'_DBA JobFailure Summary Last 24 hours',
		@enabled=1,
		@notify_level_eventlog=0,
		@notify_level_email=0,
		@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 [Email Job failures for the last day.] Script Date: 2/12/2024 2:49:51 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Email Job failures for the last day.',
		@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'DECLARE @ServerName NVARCHAR(128);
DECLARE @FailedJobsTable TABLE
(
 ServerName NVARCHAR(128),
 JobName NVARCHAR(128),
 RunDate DATETIME,
 FailureReason NVARCHAR(MAX)
);
DECLARE @HTMLBody NVARCHAR(MAX);
DECLARE @Subject NVARCHAR(255);
DECLARE @OperatorName NVARCHAR(128) = ''AdroitDBA''; -- Change this to the name of your operator

-- Get server name
SET @ServerName = (SELECT @@SERVERNAME);

-- Get failed jobs for the last 24 hours
INSERT INTO @FailedJobsTable (ServerName, JobName, RunDate, FailureReason)
SELECT
 @@SERVERNAME AS ServerName,
 J.name AS JobName,
 msdb.dbo.agent_datetime(r.run_date, r.run_time) AS RunDate,
 r.message AS FailureReason
FROM
 msdb.dbo.sysjobhistory r
 INNER JOIN msdb.dbo.sysjobs J ON r.job_id = J.job_id
WHERE
 r.run_status = 0 -- Failed jobs
 AND msdb.dbo.agent_datetime(r.run_date, r.run_time) >= DATEADD(HOUR, -24, GETDATE()); -- Last 24 hours

-- Check if there are any failed jobs
IF EXISTS (SELECT 1 FROM @FailedJobsTable)
BEGIN
 -- Prepare HTML body
 SET @HTMLBody = N''


 table { border-collapse: collapse; }
 th, td { border: 1px solid black; padding: 5px; }




### Failed Jobs on SQL Server '' + @ServerName + ''



 Server Name
 Job Name
 Run Date
 Failure Reason
 '';

 SELECT @HTMLBody = @HTMLBody + N''
 '' + ServerName + N''
 '' + JobName + N''
 '' + CONVERT(NVARCHAR, RunDate, 120) + N''
 '' + FailureReason + N''
 ''
 FROM @FailedJobsTable;

 SET @HTMLBody = @HTMLBody + N'''';


 -- Prepare email subject
 SET @Subject = N''!Failure - Jobs failed on SQL Server '' + @ServerName + '' !Failure'';

 -- Send email to operator
 EXEC msdb.dbo.sp_send_dbmail
 @recipients = ''dbas@adroitdba.com'',
 @subject = @Subject,
 @body = @HTMLBody,
 @body_format = ''HTML'';
	END;',
		@database_name=N'master',
		@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'_DBA JobFailure Summary Last 24 hours schedule',
		@enabled=1,
		@freq_type=4,
		@freq_interval=1,
		@freq_subday_type=1,
		@freq_subday_interval=0,
		@freq_relative_interval=0,
		@freq_recurrence_factor=0,
		@active_start_date=20240212,
		@active_end_date=99991231,
		@active_start_time=70000,
		@active_end_time=235959,
		@schedule_uid=N'86fb9671-9a0d-4322-8487-45363afbd56d'
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:
EXEC msdb.dbo.sp_update_job @job_id=@jobId,
		@notify_level_email=2,
		@notify_level_page=2,
		@notify_email_operator_name=N'AdroitDBA'
GO

Why Streamline This Process?

Efficiency and Timeliness: Automating the job failure notifications ensures that DBAs are informed about every failed job on a daily basis in a neat and organized list.Consistency: A standardized, automated process reduces the risk of human error and ensures that no failed job goes unnoticed. No more "I thought you were watching that job!" moments.Resource Management: Automating repetitive tasks frees up DBAs to focus on more strategic activities, optimizing resource utilization. Your DBAs will thank you for not having to play detective every morning.

Potential Drawbacks

Initial Setup: Implementing and testing the script requires an initial investment in time and resources. It's a bit like setting up a new home entertainment system—worth it, but not without a bit of effort.Maintenance: The script may need updates to adapt to changes in the database environment or organizational requirements. Just like software updates, it’s necessary to keep things running smoothly.False Positives: There is a potential for false positives or redundant notifications, which can lead to alert fatigue if not managed properly. Too many notifications can make you feel like your inbox is a drama queen.Using the provided script, you can significantly streamline your job monitoring process, ensuring that failures are swiftly identified and addressed.Schedule a free consultation: Free 15-minute consultationSee our pricing: Adroit DBA - SQL Performance, SQL Troubleshooting