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
For Enable:
SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 1' FROM msdb..sysjobs
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
0 comments:
Post a Comment