The article explain tips for SQL Server disable all sql jobs topic. All of as sometimes in our work need to do automatic jobs. One of them can disable all Jobs on SQL Server Agent. Having for example 10 jobs is very easy to disable one by one. How about 50 jobs or maybe 100 Jobs? We will need some automatic script to this jobs quickly for as. We are going to explain how to disable all sql jobs at once, using specific name or using specific category.
Why will need to disable or Enable all jobs?
You perform some task every day on midnight and need to be disabled all jobs on Agent.
Planning some database system restore.
To perform some task every month on specific day and need to be disabled all jobs.
Planning to Perform SQL Database migration.
Sql Server Disable all Sql Jobs
- Find all enabled or disabled jobs on SQL Server
- Enable or Disable all sql jobs using table updates
- Disable or Enable all jobs using stored procedures
- Disable using specific name of jobs
- Using stored procedures to Disable all jobs
- Disable jobs by Job Category
Find all enabled or disabled jobs on SQL Server
First use the following script to find Enabled jobs:
--Find enabled jobs USE msdb GO SELECT name,enabled FROM dbo.sysjobs
Disable or Enable all jobs using Table Updates
—Disable All SQL Server Agent Jobs
USE MSDB; GO UPDATE MSDB.dbo.sysjobs SET Enabled = 0 WHERE Enabled = 1; GO
--Enable All SQL Server Agent Jobs USE MSDB; GO UPDATE MSDB.dbo.sysjobs SET Enabled = 1 WHERE Enabled = 0; GO
The procedures above use update directly to msdb table and is not recommended from Microsoft.
Disable or Enable all jobs using stored procedures
Enable or disable job using stored procedures and specific name of job.
Disable or Enable jobs using specific name of job
--Disable Jobs By Job Name USE msdb ; GO EXEC dbo.sp_update_job @job_name = N'Test', @enabled = 0; GO
--Enable Jobs By Job Name USE msdb ; GO EXEC dbo.sp_update_job @job_name = N'Test', @enabled = 1; GO
Disable all sql jobs using stored procedures
This is the right procedure to enable or disable all jobs on SQL Server recommended by Microsoft.
First create commands using the following script:
-- For disable all Jobs SELECT 'EXEC msdb.dbo.sp_update_job @job_name = ''' + name + N''', @enabled = 0;' FROM dbo.sysjobs
-- For enable all Jobs SELECT 'EXEC msdb.dbo.sp_update_job @job_name = ''' + name + N''', @enabled = 1;' FROM dbo.sysjobs
The commands above will generate the scripts to disable or enable all jobs on your SQL Server using stored procedure. Will generate 1 row for every job on your Agent Jobs. Copy the scripts and execute.
The scripts will be like:
EXEC msdb.dbo.sp_update_job @job_name = 'SSIS Server Maintenance Job', @enabled = 1; EXEC msdb.dbo.sp_update_job @job_name = 'syspolicy_purge_history', @enabled = 1;
Disable jobs by Job Category
Sometimes we need to disable only jobs on specific category. To execute this task we use the following Script:
USE MSDB; GO UPDATE J SET J.Enabled = 0 FROM MSDB.dbo.sysjobs J INNER JOIN MSDB.dbo.syscategories C ON J.category_id = C.category_id WHERE C.[Name] = 'Database Maintenance'; GO
If you have any question about SQL Server disable all sql jobs feel free to ask on the comment section.
Please rate use if this article was helpful to you!