Enable Or Disable All Jobs In SQL Server

Enabling or disabling jobs is one of the most common tasks for a DBA through his or her career. There can be multiple reasons to either enable or disable a job because they can hold a lot of functionality. But there can be a situation where the entire server’s SQL Agent Jobs must be enabled or disabled. This most probably can happen when a server failover happens or any interruption to SQL Agent running the jobs.

Enabling a single job is very simple to go through the GUI steps. But they can simply freak our minds if there are hundreds of jobs in a single server to enable or disable.

A simple code mentioned below can be a life saver for DBAs.

General syntax to disable a job:

exec msdb..sp_update_job @job_name = 'your job name here', @enabled = 0

General syntax to enable a job:

exec msdb..sp_update_job @job_name = 'your job name here', @enabled = 1

Let's check to enable or disable all jobs at once.

For Disable:

SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 0' FROM msdb..sysjobs

Enable or disable all jobs sql server

For Enable:

SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 1' FROM msdb..sysjobs

Enable or Disable All Jobs in SQL Server 2

Once you execute the above code, the results generated will be all the jobs from msdb..sys.jobs scripted. Now, please execute the enable or disable code accordingly as per your desire in a new query window.

You may find this interesting:

Enable or Disable All Alerts in SQL Server

How to check database usage in SQL Server

Difference Between Clustered Index And Non-Clustered Index In SQL Server


Post a Comment