Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Enabling and Disabling SQL Agent Jobs with T-SQL

From Wiki

Jump to: navigation, search

There are two ways you can disable and enable jobs; you can use the stored procedure sp_update_job or you can update the sysjobs table directly. In general if there is a stored procedure to modify something, you should use it instead of updating (system) tables directly.

The code below assumes you have a job named MyJob1

To disable the job by using the stored procedure sp_update_job

  1. exec msdb..sp_update_job @job_name = 'MyJob1', @enabled = 0


To enable the job by using the stored proceduresp_update_job

  1. exec msdb..sp_update_job @job_name = 'MyJob1', @enabled = 1


To disable the job by updating the sysjobs table

  1. UPDATE MSDB.dbo.sysjobs
  2. SET Enabled = 0
  3. WHERE [Name] =( 'MyJob1')


To enable the job by updating the sysjobs table

  1. UPDATE MSDB.dbo.sysjobs
  2. SET Enabled = 1
  3. WHERE [Name] =( 'MyJob1')




One advantage of updating the sysjobs directly is that you can disable/enable more than one job with one statement. For example if you want to disable all jobs that were created before January 1 2010, you would execute the following statement

  1. UPDATE MSDB.dbo.sysjobs
  2. SET Enabled = 0
  3. WHERE date_created < '20100101'




Contributed by: --SQLDenis 17:15, 5 April 2010 (GMT)

Part of SQL Server Admin Hacks

679 Rating: 2.8/5 (44 votes cast)