Jeff Prom's SQL Server Blog

Sharing knowledge and tips on SQL Server, Business Intelligence and Data Management.

Failed Job Steps That Didn’t Notify An Operator

Posted by jeffprom on March 16, 2010

I often use jobs that have numerous steps. One example would be a job that has some initial prep work, multiple steps that run similar code on separate databases for multiple stores, and then finally some cleanup steps. I break the job down to individual steps per store so that if one of the steps fails, they all don’t fail. To do this, go to the advanced tab of the job step properties and set the ‘On failure action:‘ to ‘Go to the next step’. This allows the job to continue processing if there is an error on one of the steps. However, now it raises the question of how do we know if a step failed within a job? Unfortunately Microsoft doesn’t have built in failure notification for steps like they do with the overall job. You can view the job’s history and see if a step failed by the yellow icon, but that is not practical to check every day especially if you have multiple jobs setup this way. A better solution is to use the code below which shows jobs that recently had failed steps and did not notify an operator. It can be handy to setup in a SSRS report to keep an eye on all of your jobs that had failed steps.

-- FAILED JOB STEPS THAT DIDN'T NOTIFY AN OPERATOR VIA EMAIL
USE msdb
GO

DECLARE @DateStringToday VARCHAR(8);
DECLARE @DateStringYesterday VARCHAR(8);

SET @DateStringToday = convert(varchar, getdate(), 112);
SET @DateStringYesterday = convert(varchar, getdate()-1, 112);

SELECT
job_name = sj.name,
sj.enabled,
sjh.step_id,
sjh.step_name,
sjh.sql_message_id,
sjh.sql_severity,
sjh.message,
sjh.run_status,
sjh.run_date,
sjh.run_time,
sjh.run_duration,
operator_emailed = so.name

FROM msdb.dbo.sysjobhistory as sjh
INNER JOIN msdb.dbo.sysjobs_view sj ON sj.job_id = sjh.job_id
LEFT OUTER JOIN msdb.dbo.sysoperators so ON (sjh.operator_id_emailed = so.id)

WHERE sjh.run_status = 0
AND sjh.run_date IN(@DateStringToday, @DateStringYesterday) -- show today and yesterday
AND sj.enabled = 1 -- make sure it's enabled
AND sj.category_id != '101' -- remove SSRS report process jobs
AND so.name IS NULL -- show jobs that didn't already email an operator

ORDER BY sjh.run_date DESC, sjh.run_time DESC

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: