The proper way to disable/enable SQLServer Agent Jobs

Question

I have a number of SQLServer agent scheduled jobs, one of them performs a full database backup. I want to disable some other jobs when backup begins and re-enable them once backup is done. What is the right way to do so? I was thinking about adding one of the following tsql commands to the first step of the backup task (and respective enable commands to the last step), but I cannot find which one is better (or maybe there is another way).

UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE [Name] IN (....)

Or a number of EXEC dbo.sp_update_job ?
Thanks.

So which one do you think is better? `UPDATE sysojobs` directly or by using `sp_update_job` ? — a1ex07, Mar 23 '11 at 15:47
what are you going to do about the disabled jobs if/when the backup fails? — DForck42, Mar 23 '11 at 17:11
@DForck42: I incline to gbn's solution with applocks, but there are some pitfalls - the lock acquired at step 1 doesn't seem to be held at step 2. — a1ex07, Mar 23 '11 at 17:46

score 6 · Answer 1 · answered Mar 23 '11 at 19:56

6

Definitely use sp_update_job. If the job is already scheduled, then manipulating the sysjobs table directly won't necessarily cause the cached schedule to be re-calculated.

It might work for the ENABLED flag (haven't tried it), but I know for a fact that it doesn't work for columns like start_step_id.

answered Mar 23 '11 at 19:56

BradC

39,306
13
73
89

2

Correct - updating the sysjobs table directly will break existing schedules – soslo Mar 01 '13 at 18:02

gbn · Accepted Answer · 2011-03-23T15:54:39.163

4

You'd have to run EXEC dbo.sp_update_job because you can't update system tables directly (~~although I'm not sure if sysjobs still counts as a system table~~ Mitch says it can be updated)

I would consider the use of sp_getapplock and sp_releaseapplock to "lock" other jobs out without actually updating the jobs though.

edited Mar 23 '11 at 15:54

answered Mar 23 '11 at 15:49

gbn

422,506
82
585
676

@Mitch Wheat: haven't tried it for some years and can't in my current enviroment – gbn Mar 23 '11 at 15:53
Thanks for locks hint. `sysjobs` seem to be updatable, I've just tried to add tsql script to the job steps and it worked. – a1ex07 Mar 23 '11 at 15:59
I tried using locks, but I got the following problem: the lock obtained on the first step of agent job seems to be released after first step completed ( I did `EXEC @result = sp_getapplock @Resource = 'TestLock', @LockOwner='Session',@LockTimeout =5000, @LockMode = 'Exclusive';`). Is there a workaround? – a1ex07 Mar 23 '11 at 17:47
@a1ex07: I'd put it into the main job step, or wrap the main call in a stored proc. Each step will execute separately on a separate connection so a session lock goes out of scope. – gbn Mar 23 '11 at 20:10
@gbn: Thanks, I thought maybe I missed something. – a1ex07 Mar 23 '11 at 20:29

score 2 · Answer 3 · answered Mar 23 '11 at 15:56

2

I would use sp_update_job as it encapsulates reusable piece of logic that is supported. Why re-invent the wheel.

http://msdn.microsoft.com/en-us/library/ms188745.aspx

answered Mar 23 '11 at 15:56

Kuberchaun

29,160
7
51
59

score 1 · Answer 4 · answered Mar 23 '11 at 15:48

1

I don't see anything wrong with your suggested approach. You can also manipulate via job category:

UPDATE j
SET j.Enabled = 0
FROM MSDB.dbo.sysjobs j
INNER JOIN MSDB.dbo.syscategories c ON j.category_id = c.category_id
WHERE c.[Name] = 'Database Maintenance';

I haven't profiled it, but I suspect

USE msdb ;
GO

EXEC dbo.sp_update_job
    @job_name = N'SomeJob',
    @enabled = 0;
GO

Will be generating the same code, but the builtin procs are usually the way to go.

answered Mar 23 '11 at 15:48

Mitch Wheat

295,962
43
465
541

1

Wouldn't recommend updating sysjobs table directly, because cached schedules won't properly update. See my answer. – BradC Mar 23 '11 at 19:59

score 1 · Answer 5 · edited Oct 26 '12 at 21:09

SQL Agent caches the enabled status of jobs. So if you simply update the sysjobs table it wont actually prevent a schedule from triggering the job. The sp_update_job stored procedure does trigger the cache to update, so I recommend you use that.

If you still want to manually set the value in sysjobs, you have to run sp_sqlagent_notify to actually get sql agent refresh is cache of the enabled status. Just look at the code of sp_update_job for the exact parameters you need.

score 0 · Answer 6 · answered Mar 23 '11 at 19:48

The other approach would be to add a step at the beginning of your other jobs which checks the status of the backup job and then either aborts or sleeps the current job if the backup is running.

We have done it both ways at times, depends on how reliable/critical the different jobs are which one works better.

The proper way to disable/enable SQLServer Agent Jobs

6 Answers6