Notify Operator if ANY step in job fails

Question

Can I (How do I) configure Sql Server 2008 to notify an operator if any step in the Job fails?

I have a Sql Server job with several steps to update data from multiple different sources, followed by one final step which performs several calculations on the data. All of the "data refresh" steps are set to "Go to next step on failure". Generally speaking, if one of the data refreshes fails, I still want the final step to run, but I still want to be notified about the intermediate failures, so if they fail consistantly, I can investigate.

Hileman

Answer 1

Here is how we do it. We add one last T-SQL step (usually called "check steps") with this

SELECT  step_name, message
FROM    msdb.dbo.sysjobhistory
WHERE   instance_id > COALESCE((SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory
                                WHERE job_id = $(ESCAPE_SQUOTE(JOBID)) AND step_id = 0), 0)
        AND job_id = $(ESCAPE_SQUOTE(JOBID))
        AND run_status <> 1 -- success

IF      @@ROWCOUNT <> 0
        RAISERROR('Ooops', 16, 1)

Notice that this code is using tokens in job steps (the $(...) part), so code can't be executed in SSMS as is. It basicly tries to find entries of previous steps of the current job in sysjobhistory and looks for failure statuses.

In Properties->Advanced you can also check Include step output in history to get the message from step failure. Leave the On failure action to Quit the job reporting failure.

Dysprosium

Pretty and ingenious. I've extended it into something a bit less pretty but helpful; see my answer. – Yirinec

Answer 2

@wqw 's accepted answer is excellent.

I've extended it for those who have Database Mail enabled to email a bit more detail about exactly what failed and how. Also incorporates icvader's answer on this page to take account of retries.

Should be really helpful for those of us who need more detail to judge whether urgent action is required when offsite/on-call.

DECLARE 

@YourRecipients as varchar(1000) = '[email protected]'
,@YourMailProfileName as varchar(255) = 'Database Mail'

,@Msg as varchar(1000)
,@NumofFails as smallint
,@JobName as varchar(1000)
,@Subj as varchar(1000)
,@i as smallint = 1


---------------Fetch List of Step Errors------------
SELECT *
INTO #Errs

FROM

    (
    SELECT 
      rank() over (PARTITION BY step_id ORDER BY step_id) rn
    , ROW_NUMBER() over (partition by step_id order by run_date desc, run_time desc) ReverseTryOrder
    ,j.name job_name
    ,run_status
    , step_id
    , step_name
    , [message]

    FROM    msdb.dbo.sysjobhistory h
    join msdb.dbo.sysjobs j on j.job_id = h.job_id

    WHERE   instance_id > COALESCE((SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory
                                    WHERE job_id = $(ESCAPE_SQUOTE(JOBID)) AND step_id = 0), 0)
            AND h.job_id = $(ESCAPE_SQUOTE(JOBID))
    ) as agg

WHERE ReverseTryOrder = 1 ---Pick the last retry attempt of each step
  AND run_status <> 1 -- show only those that didn't succeed 


SET @NumofFails = ISNULL(@@ROWCOUNT,0)---Stored here because we'll still need the rowcount after it's reset.


-------------------------If there are any failures assemble email and send ------------------------------------------------
IF  @NumofFails <> 0
    BEGIN

        DECLARE @PluralS as char(1) = CASE WHEN @NumofFails > 1 THEN 's' ELSE '' END ---To make it look like a computer knows English
        SELECT top 1 @Subj = 'Job: ' + job_name + ' had ' + CAST(@NumofFails as varchar(3)) + ' step' + @PluralS + ' that failed'
                    ,@Msg =  'The trouble is... ' +CHAR(13) + CHAR(10)+CHAR(13) + CHAR(10)

                        FROM dbo.#Errs


        WHILE @i <= @NumofFails 
        BEGIN
            SELECT @Msg = @Msg + 'Step:' + CAST(step_id as varchar(3)) + ': ' + step_name  +CHAR(13) + CHAR(10)

            + [message] +CHAR(13) + CHAR(10)+CHAR(13) + CHAR(10) FROM dbo.#Errs
            WHERE rn = @i


            SET @i = @i + 1
        END

            exec msdb.dbo.sp_send_dbmail
            @recipients = @YourRecipients,
            @subject = @Subj,
            @profile_name = @YourMailProfileName,
            @body = @Msg


    END

One difference from the other answers on which its based: Doesn't raise the whole job as an error. That's to retain the distinction in job history between Aborted and Completed with Errors.

Yirinec

Answer 3

An improvement to the above answer, in case somebody wants to use the operators in sql server agent to send email; and use the database profile name stored in msdb:

DECLARE @EmailRecipients as varchar(1000)
DECLARE @MailProfileName as varchar(255)
DECLARE @Msg as varchar(1000)
DECLARE @NumofFails as smallint
DECLARE @JobName as varchar(1000)
DECLARE @Subj as varchar(1000)
DECLARE @i as smallint = 1

SELECT @EmailRecipients = email_address 
FROM msdb.dbo.sysoperators
WHERE name = <Operator Name>

SELECT TOP(1) @MailProfileName = name 
FROM msdb.dbo.sysmail_profile

SELECT * INTO #Errs
FROM
    (SELECT rank() over (PARTITION BY step_id ORDER BY step_id) rn, 
            ROW_NUMBER() over (partition by step_id order by run_date desc, run_time desc) ReverseTryOrder,
           j.name job_name,
           run_status,
           step_id,
           step_name,
           [message]
     FROM msdb.dbo.sysjobhistory h
     JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
     WHERE instance_id > COALESCE((SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory
                                    WHERE job_id = $(ESCAPE_SQUOTE(JOBID)) AND step_id = 0), 0)
     AND h.job_id = $(ESCAPE_SQUOTE(JOBID))
    ) AS agg
WHERE ReverseTryOrder = 1 ---Pick the last retry attempt of each step
AND run_status <> 1 -- show only those that didn't succeed 


SET @NumofFails = ISNULL(@@ROWCOUNT,0)---Stored here because we'll still need the rowcount after it's reset.

IF  @NumofFails <> 0
BEGIN
    DECLARE @PluralS as char(1) = CASE WHEN @NumofFails > 1 THEN 's' ELSE '' END

    SELECT top 1 @Subj = job_name + ':'+ CAST(@NumofFails as varchar(3)) + '''Check Steps'' Report',
                 @Msg =  '''Check Steps'' has reported that one or more Steps failed during execution of ' + job_name + CHAR(13) + CHAR(10)+ CHAR(13) + CHAR(10)
    FROM dbo.#Errs

    WHILE @i <= @NumofFails 
    BEGIN
        SELECT @Msg = @Msg + 'Step ' + CAST(step_id as varchar(3)) + ': ' + step_name  +CHAR(13) + CHAR(10)
                     + [message] +CHAR(13) + CHAR(10)+CHAR(13) + CHAR(10) 
        FROM dbo.#Errs
        WHERE rn = @i

        SET @i = @i + 1
    END

    EXEC msdb.dbo.sp_send_dbmail
    @recipients = @EmailRecipients,
    @subject = @Subj,
    @profile_name = @MailProfileName,
    @body = @Msg
END

Masterpiece

I've not looked at this closely but I'll say one thing. It's a damn sight better styled than the way I used to write SQL back then. – Yirinec

Answer 4

I have most of my steps set to retry due to a unique translog scenario that causes occasional blocking. wqw's post will alert even if a step has successfully retried. I have made an adaptation that will not alert if a step has failed, but then was a success on retry.

SELECT  step_id, MIN(run_status)
FROM    msdb.dbo.sysjobhistory
WHERE   instance_id > COALESCE((SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory
                                WHERE job_id = $(ESCAPE_SQUOTE(JOBID)) AND step_id = 0), 0)
        AND job_id = $(ESCAPE_SQUOTE(JOBID))
GROUP BY step_id
HAVING MIN(run_status) <> 1 -- success

IF @@ROWCOUNT <> 0
RAISERROR('FailedStep', 16, 1)

Poorhouse

Answer 5

Adamantish's answer is the perfect solution (Thank you) : worked flawlessly.. minor edits. As wqw stated before, will not work in SSMS, add this as a last step and run the job.

WHERE instance_id > COALESCE
(
(
SELECT MAX(instance_id) 
FROM msdb.dbo.sysjobhistory
WHERE job_id = '2XXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXXX' AND step_id = 0), 0
)
AND h.job_id = '2XXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXXX'
) 
as agg

Rumal

Answer 6

Go to Job Properties > Notification tab > action to perform when the job completes

under that check the Email checkbox and select "When the job fails" from the dropdown and save the job.

Read 4th point on http://msdn.microsoft.com/en-us/library/ms191130.aspx

If you want to notify an operator by e-mail, check E-mail, select an operator from the list, and then select one of the following:

• When the job succeeds: to notify the operator when the job completes successfully.

• When the job fails: to notify the operator when the job completes unsuccessfully.

• When the job completes: to notify the operator regardless of completion status.

Buttermilk

Unfortunately, this does not solve the problem. First, this will email upon completion, regardless of success or failure. I would expect to see emails only if a step failed. -- Also, the completion email does not include any text indicating an individual step failed. It simply states "The job succeeded." How can I know that a specific step failed. – Hileman

use this option "When the job fails" to notify the operator when the job completes unsuccessfully.It means you wil get notified if the job completed with any failed steps(it will complete the job even if any step fails as on error go to next step was specified for the job steps). then you can view job history for further details.. what say! – Buttermilk

The option for "When the job fails" does not send an email because the last step succeeded (aka "Quit the job reporting success") – Hileman

Answer 7

in each step add code:

if @@error > 0
EXEC sp_send_dbmail @profile_name='DBATeam',
@[email protected]',
@subject='SomeJob SomeStep failed',
@body='This is the body of SomeJob SomeStep failed' 

Allness