The proper way to disable/enable SQLServer Agent Jobs

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.

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.

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.

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

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.

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.

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.