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.

Solution 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.

Solution 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.

Solution 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

Solution 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.

Solution 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.

View more solutions

Comments

[ZygD]

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

[a1ex07]

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]

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?

[topski]

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

[ZygD]

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

[a1ex07]

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

[soslo]

Correct - updating the sysjobs table directly will break existing schedules