Chaining Agent Tasks For Alerting The Right Way

Step By Step

When you think about setting up maintenance, a persistent fear is that tasks may step on each other. You probably don’t want backups and CHECKDB and statistics maintenance running at the same time as statistics maintenance (notice I didn’t say index maintenance, here, because you’re smart and you skip it?). You may also throw in some other steps, like cleanup tasks something.

Your next fear is that if one step fails, you won’t go to the next step. For instance, a Sort from Statistics maintenance may fill up TempDB, a drive for backups may run out of space (or the network path could get yoinked), or there might be some (DEARPLEASEGODDONTLETITBECORRUPTION) error associated with DBCC CHECKDB (like one of those weird NTFS fragmentation errors) or something.

At no point should one step failure lead to another step not running, so you do something like this.

On failure report success: Government work at its finest

The trouble is, when a step fails, there’s no way for you to get a notification that a step failed (unless you write some custom code within the job).

Bummerino mi amigo.

Soldier Forth

In The Know

The smart thing to do is use built-in stored procs to call other jobs.

Pants: On

This allows you to retain control over job run order, and still get emails when individual job steps fail, because each job can report and alert on failure.

Update Antony correctly points out that this will go out and run your jobs asynchronously. There’s a second part to this where I discuss how to control that further.

Thanks for reading!

,
Previous Post
Locking When There’s Nothing To Lock
Next Post
[Video] Office Hours 2017/08/02 (With Transcriptions)

18 Comments. Leave new

  • Since start job sets the job running and then returns, this would end up running your jobs in parallel, not in sequence.

    • Erik Darling
      August 4, 2017 9:10 am

      Yep! It takes some extra legwork to get them running as steps (which I’m working on another post about). This is just to help folks who need to get alerts for failures and have jobs continue. I’ll update the post to make that more clear.

      Thanks!

  • Another way to do this is to setup an agent job that queries msdb..sysjobsteps and jobhistory to find failed jobs, get all of the details from the failed job, and then use sp_send_dbmail to email that to your admins. All of this can be done in a simple script in an Agent job. This wouldn’t work in all situations but most of the time when we set “Continue on failure” it’s not a critical step, if it was then we would fail the step and have it page an after-hours admin.

  • A simple way I have done it in the past is to have every other step in the job be a failure email step. Each real step would continue to the next step on failure (so you get an email alert) or skip over the next step on success. The failure email step would simply go to the next step on both failure and success. That would let me track the success/failure of each step in the job.

  • Hi Erik,
    This is a common issue that most sql server shops will face and this is a timely discussion for m shop.
    Can I tell my manager (LOL) you will shortly provide the second post for how to make the executions synchronous?

    And Seth’s suggestion seems obviously simple and straightforward, although perhaps involves a fair amount of redundant code…

    • As long as you don’t mind a little looping code, sure.

      Otherwise, there are third party tools out there that make job chaining simple (SentryOne has Event Manager, for instance).

      Thanks!

      • without a few loops here and there we wouldn’t see much useful programming deployed in the world, (thought not so much in sql for very good reason)…

  • You say: “notice I didn’t say index maintenance, here, because you’re smart and you skip it?”
    Can you explain me why are you saying that we didn’t need index maintenance? This mean’s that we never have to do index maintenance?

    • Maty,

      Unless you need to change something about an index (rebuild to change fill factor, etc.), I typically recommend skipping index maintenance. There are plenty of blog posts around our site about why.

      Thanks!

      • Erik,

        Can you send me a link for that blog posts?

        • Erik Darling
          August 4, 2017 1:55 pm

          Sure — start here.

          • I read the blog post.
            But can you tell my why this is your recommendation?
            I’m confused now, because I read in Microsoft book, on SQL site, listened on Microsoft education, and they all said we needed to find time to index maintenance. Why did Ola write the Index Maintenance script if we did not this?
            Can you explain? Do you mean only for SSD or generally?

          • Maty – the reason why is in the blog post. Going into more personalized training detail than that is kinda beyond the scope of what we can do in a blog post. Feel free to search our site for “fragmentation” and you’ll see lots of other posts about it, though.

  • Exactly what I do Erik – great to share that information! There are a lot of ways to do this sort of thing, but this method is the most straight forward – just alert the operator if something fails and drive on.

  • When using on failure go to the next, I add a final step to the job to report any errors.
    http://stackoverflow.com/questions/3858472/notify-operator-if-any-step-in-job-fails

    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(‘A job step failed!’, 16, 1)

  • me too.
    instead of just raising, with something like
    “`
    exec Myown.dbo.NotifySQLDBA ‘Error’, ‘$(ESCAPE_SQUOTE(JOBID))’, ‘$(ESCAPE_SQUOTE(STRTDT))’, ‘$(ESCAPE_SQUOTE(STRTTM))’
    “`
    I pile up eventual error logs to be sent via mail

    “`SELECT
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    js.output_file_name,
    ‘$(ESCAPE_SQUOTE(STEPID))’, js.step_id),
    ‘$(ESCAPE_SQUOTE(JOBID))’, @JOBID),
    ‘$(ESCAPE_SQUOTE(STRTDT))’, @STRTDT),
    ‘$(ESCAPE_SQUOTE(STRTTM))’, @STRTTM)
    FROM msdb.dbo.sysjobhistory jh
    inner join msdb.dbo.sysjobsteps js
    on js.job_id = jh.job_id
    where UPPER(master.dbo.fn_varbintohexstr(jh.job_id)) = @JOBID
    AND js.output_file_name IS NOT NULL
    AND msdb.dbo.agent_datetime(run_date, run_time) >= msdb.dbo.agent_datetime(@STRTDT, @STRTTM)“`

  • […] MS SQL Docs: SQL Server Agent • MS SQL Docs: sp_start_job • BrentOzar.com: Chaining Agent Tasks For Alerting The Right Way, by Erik Darling • LogicalRead: Scheduling SQL Server Jobs with SQL Agent, by Michael Otey • MS […]

Menu
{"cart_token":"","hash":"","cart_data":""}