Sql Server : How to enable or disable Sql Agent Jobs using T-SQL

Quick one today:

Occasionally, during deployments, we need disable some Sql Agent jobs as we are making pertinent changes. If it is just one or two, may be doing it manually is okay (not really, but it seems to be more common), but when we have a large list of jobs that need to be disabled and then enabled, it’s always a better idea to rely on T-SQL scripts. Less room for human error.

For this, Sql Server has an internal stored procedure called ‘sp_update_job‘. As it seems obvious, it could do more than just enable/disable jobs. But the example below, only shows one aspect of it.

By change the parameters, we could change many properties of a Sql Agent Job.

--
--  Change Sql Agent Job parameters
--
USE msdb
GO

EXEC dbo.sp_update_job     @job_name = N'JobName'
			,  @enabled = 1
GO

 

Hope this helps,
_Sqltimes

Advertisements