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.
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.
In The Know
The smart thing to do is use built-in stored procs to call other jobs.
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!
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.
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?
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)
That should be IF @@ROWCOUNT <> 0
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
SELECTREPLACE(
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 […]