SQL Agent Jobs: Checking for failed steps at the end of a job

I use the SQL agent a lot, and it is handy for a lot of things, but  it can be frustrating to not be able to pass state information between steps.

For example, I have a job where I want to execute data verification steps against multiple tables. It makes sense to have the check for each table in its own step with a clear label to simplify troubleshooting– so when the job fails, you can see which step had an error and know from the name exactly what’s wrong.  But I want all steps in the job to run, regardless of whether a step fails— I want to check for failure at the end.

The most basic way to do this is to have each job step log to a table. This isn’t really bad, but I’d rather not maintain a table for every job of this type. It leaves room for failure, it’s more to maintain, and it just feels redundant for  the most part: all of the job history  is tracked in MSDB anyway, shouldn’t I be able to use that?The requirements I have for the job are:

  • Each step must be relatively short and do a discrete unit of work.
  • Each step will raiseerror with log if it has a problem, but still continue on to the next step.
  • At the end of the job the final step checks see if any previous step in the current run has failed. If so, it fails the job itself, otherwise the job will succeed.
  • The last step should list all the steps that have failed, just for ease of use.

So far this is working in my tests as the code for the final step, and it meets my needs for this type of job. Note: This uses a token to determine the job_id of the running job, so this will only work within the execution context of a SQL Agent job. If you want to test in a regular query, you’ll have to assign a fake job_id for your test.  SQL Agent steps also don’t parse tokens successfully (at least in the version I’m using).

set quoted_identifier on;
declare @errorMsg nvarchar(max);

select
    @errorMsg= 'The following steps failed, please investigate:' + LEFT(o.list, LEN(o.list)-1)
from (
    select
        [text()] = step_name + ','
    FROM msdb.dbo.sysjobhistory jh
    join msdb.dbo.sysjobactivity ja on
        jh.job_id=ja.job_id
    where
        run_status=0 --step failed
        and step_id != 0
        and jh.job_id = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)))
        and --this block ensures that we just pull information from the most recent job run
        (
            -- The start time of the step, converted to datetime
            CONVERT (DATETIME, RTRIM(run_date))
            + ( run_time * 9
                + run_time % 10000 * 6
                + run_time % 100 * 10
            ) / 216e4
            >= ja.start_execution_date -- the time the job last started
        )
    order by instance_id
    FOR XML PATH ('')
) o (list)

if @errorMsg is null
    print ' Everything looks good...'
else
begin
    raiserror (@errorMsg, 16, 1)
end
Next Post
A Table Summarizing All Agent Jobs with Steps…

Related Posts

7 Comments. Leave new

  • I tried this and had ptroblems with it reporting on more than the most recent time the job ran, basically it returned all failed events ever. I tweaked the where clasue slightly and it seems to have straightened up for me at least…
    There is one addition to the where clause which is flagged below:
    Cheers

    where
    — ** new clause start ** —
    ja.session_id=(SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity)
    AND
    — ** new clause end ** —
    run_status=0 –step failed
    and step_id != 0
    and jh.job_id = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)))
    and –this block ensures that we just pull information from the most recent job run
    (
    — The start time of the step, converted to datetime
    CONVERT (DATETIME, RTRIM(run_date))
    + ( run_time * 9
    + run_time % 10000 * 6
    + run_time % 100 * 10
    ) / 216e4
    >= ja.start_execution_date — the time the job last started
    )

    Reply
    • Awesome that you looked at this and improved it! I had to look back at the post– indeed, I wrote “so far this is working in my tests.” If I recall correctly, I wrote this when we were prototyping different ideas for how to manage state in some of our data processing. And this method didn’t get picked, so it never got the FULL round of testing.

      I was just thinking about the problem of state and the SQL Agent recently though, so I love that you’ve made something of this.

      Reply
  • Hi,
    Do you recall which method you ended up using in place of this prototype and why it was chosen?

    Reply
  • Hi Kendra
    You are simply GREAT. All you work continually helps me. Thank you so much. Now the question. I have a failed job step that puts the server name and job name in an email as the last step of our production jobs. It would be great to add the failing sttep(s). The problem is that I simply don’t have time to go back and put raiserror or throw statements in each step. Other than a table to hold the failing step info (which I don’t have time to retrofit) is there an alternative to raiserror so that I can avoid retrofits. Many thanks, again, for all you do.

    Reply
    • Thanks so much for the kind words!

      I’m guessing it’s a stored procedure that sends the email. Could the procedure just query MSDB for recently failed jobs, including step info, and include that in the body of the email?

      It’s a little bit of work to build an HTML table in an email, but nothing too terrible. Since you mention you’re short on time, guessing that might be out of scope.

      Reply
  • As always you are the BEST.
    Tried using jobhistory, etc but the problem is that the job in question is still running per se.
    Say a 3 step job with the failure notification as step 4. If any step fails control is transferred to step 4 but the job is still running until step 4 finishes.
    Any thoughts, guidance, etc would be more than wonderful.
    Please keep up the good work
    P.S. I get a kick out of your art work.

    Reply
  • Still helpful 9 years later, thanks very much Kendra

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu