Problem
As a portion of our backup and recovery process I need to disabled (or enable) all of our SQL Server Agent Jobs depending on the situation and the SQL Server instance. I know I can use the GUI to click through and make the changes, but on a number of our SQL Server instances we have 50+ jobs, so this is a monotonous task. Do you have any scripts to enable or disable all SQL Server Jobs? What about enabling or disabling all SQL Server Jobs by Job name or Job Category name?
Solution
The SQL Server Agent system tables can be updated without setting any sp_configure parameters, or an equivalent command, as is the case with the Master database. So building a script to meet your needs should be simple and straightforward. Before we dive into some scripts to address your need, another option to consider is just stopping or starting the SQL Server Agent service. Since the SQL Server Agent service is responsible for permitting the Jobs to run in the first place, then stopping and starting this service would also prevent and then enable the Jobs to run. The one short coming to that approach may be that you might not always have rights to manage the service or that only specific types of jobs need to be enabled or disabled. As those conditions arise, using the scripts below may be the best approach.
Check Current Staus of SQL Agent Jobs
To check the current status of SQL Agent Jobs we can run these queries:
SELECT job_id, name, enabled
FROM msdb.dbo.sysjobs
-- or this version
SELECT SJ.job_id, SJ.name, SJ.enabled, SC.name as category
FROM msdb.dbo.sysjobs SJ
INNER JOIN msdb.dbo.syscategories SC on SJ.category_id = SC.category_id
Disable All SQL Server Agent Jobs
This code will disable any job that is currenlty enabled.
USE MSDB;
GO
DECLARE @job_id uniqueidentifier
DECLARE job_cursor CURSOR READ_ONLY FOR
SELECT job_id
FROM msdb.dbo.sysjobs
WHERE enabled = 1
OPEN job_cursor
FETCH NEXT FROM job_cursor INTO @job_id
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_update_job @job_id = @job_id, @enabled = 0
FETCH NEXT FROM job_cursor INTO @job_id
END
CLOSE job_cursor
DEALLOCATE job_cursorEnable All SQL Server Agent Jobs
This code will enable any job that is currenlty disabled.
USE MSDB;
GO
DECLARE @job_id uniqueidentifier
DECLARE job_cursor CURSOR READ_ONLY FOR
SELECT job_id
FROM msdb.dbo.sysjobs
WHERE enabled = 0
OPEN job_cursor
FETCH NEXT FROM job_cursor INTO @job_id
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_update_job @job_id = @job_id, @enabled = 1
FETCH NEXT FROM job_cursor INTO @job_id
END
CLOSE job_cursor
DEALLOCATE job_cursorDisable Jobs By Job Name
This code will disable any job that is currenlty enabled and the job name starts with ‘Admin’.
USE MSDB;
GO
DECLARE @job_id uniqueidentifier
DECLARE job_cursor CURSOR READ_ONLY FOR
SELECT job_id
FROM msdb.dbo.sysjobs
WHERE enabled = 1
AND [name] like N'Admin%'
OPEN job_cursor
FETCH NEXT FROM job_cursor INTO @job_id
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_update_job @job_id = @job_id, @enabled = 0
FETCH NEXT FROM job_cursor INTO @job_id
END
CLOSE job_cursor
DEALLOCATE job_cursor
Disable Jobs By Job Category
This code will disable any job that is currenlty enabled and the job category is ‘Database Maintenance’.
USE MSDB;
GO
DECLARE @job_id uniqueidentifier
DECLARE job_cursor CURSOR READ_ONLY FOR
SELECT SJ.job_id
FROM msdb.dbo.sysjobs SJ
INNER JOIN msdb.dbo.syscategories SC on SJ.category_id = SC.category_id
WHERE SJ.enabled = 1
AND SC.[name] = N'Database Maintenance'
OPEN job_cursor
FETCH NEXT FROM job_cursor INTO @job_id
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_update_job @job_id = @job_id, @enabled = 0
FETCH NEXT FROM job_cursor INTO @job_id
END
CLOSE job_cursor
DEALLOCATE job_cursor
Next Steps
- Depending on your needs and your rights, keep in mind that SQL Server Agent can be stopped to prevent Jobs from running. When you need them to run based on the schedule simply enable SQL Server Agent and the Jobs should fire as expected.
- Be creative and change the queries to meet your needs for specific jobs to enable or disable.
- If you are concerned about someone inadvertently starting SQL Server Agent which would permit the Jobs to run or if you need greater flexibility, consider the scripts from this tip.
- Check out these related tips:
- MSSQLTips.com Category: SQL Server Agent
- MSSQLTips.com Category: DBA Best Practices
Jeremy Kadlec is a Founder, Editor and Author at MSSQLTips.com with more than 300 contributions and 25+ years of SQL Server experience. Jeremy leads a team of more than 300 authors helping millions of SQL Server professionals around the globe every second of the day for the last 20 years. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP based on his community contributions. Jeremy brings 25+ years of SQL Server DBA and Developer knowledge to the community and holds a bachelor’s degree from SSU and master’s degree from UMBC.
