The proper way to disable/enable SQLServer Agent Jobs

sql-server sql-server-agent
a1ex07 picture a1ex07 · Mar 23, 2011 · Viewed 16k times · Source

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.

Answer

BradC picture BradC · Mar 23, 2011

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.

Related questions

Cannot bulk load because the file could not be opened. Operating System Error Code 3

I'm trying to set up a Stored Procedure as a SQL Server Agent Job and it's giving me the following error, Cannot bulk load because the file "P:\file.csv" could not be opened. Operating system error code 3(failed to …

Read More
How do I execute a stored procedure in a SQL Agent job?

I am using SQL Server 2008 and I need to run a SQL Job from SQL Server Agent. I am new to SQL Server Job and I want to execute a stored procedure regularly from a SQL Server Job. But I …

Read More
Could not obtain information about Windows NT group user

I am creating a SQL Server Replication using a script. When I try to execute The job failed. Unable to determine if the owner (STAR\moorer7) of job L3BPT2M-Atlas-14 has server access (reason: Could not obtain information about …

Read More