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.

Hannus

I don't see anything wrong with your suggested approach. – Pinochle

So which one do you think is better? UPDATE sysojobs directly or by using sp_update_job ? – Hannus

what are you going to do about the disabled jobs if/when the backup fails? – Mchenry

@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. – Hannus

Answer 1

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.

Wisp

@Mitch Wheat: haven't tried it for some years and can't in my current enviroment – Wisp

Thanks for locks hint. sysjobs seem to be updatable, I've just tried to add tsql script to the job steps and it worked. – Hannus

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? – Hannus

@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. – Wisp

@gbn: Thanks, I thought maybe I missed something. – Hannus

Answer 2

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.

Redd

Correct - updating the sysjobs table directly will break existing schedules – Ovate

Answer 3

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

Nuzzle

Answer 4

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.

Pinochle

Wouldn't recommend updating sysjobs table directly, because cached schedules won't properly update. See my answer. – Redd

Answer 5

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.

Dedra

Answer 6

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.

Dna