Disabling or Enabling SQL Server Agent Jobs

By:   |   Updated: 2022-02-25   |   Comments (18)   |   Related: > SQL Server Agent


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_cursor

Enable 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_cursor

Disable 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:


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2022-02-25

Comments For This Article




Friday, April 1, 2016 - 9:43:22 AM - Simon Evans Back To Top (41117)

 As per other comments this code is incorrect you need to use  sp_update_job

 

Example

EXEC msdb.sp_update_job @job_name = 'MyJob' , @enabled = 0;

 

 

Thursday, February 18, 2016 - 5:37:28 AM - Anurag Khare Back To Top (40711)

 

 Hi All,

 

I am using this way to disable jobs but somehow in job is getting executed at the scheduled time even if job is diabled.

 

Pls help me on this as some client report is getting generated wrongly

 

-Anurag Khare

Friday, January 29, 2016 - 12:30:25 PM - Kevin Back To Top (40545)

Hi,

The script in this article marks the jobs as disabled but the jobs still run!

The script in this link - http://weblogs.sqlteam.com/tarad/archive/2003/10/17/325.aspx - actually does disable the jobs.

Here is a discussion on this issue: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159077

SCRIPT:

CREATE PROC isp_Disable Jobs

AS

SET NOCOUNT ON

CREATE TABLE #Job_Names
(
 Job_Name SYSNAME NOT NULL
)

INSERT INTO #Job_Names
SELECT name
FROM msdb.dbo.sysjobs
ORDER BY name

DECLARE @job_name SYSNAME
DECLARE @job_id UNIQUEIDENTIFIER

DECLARE disable_jobs CURSOR FOR
SELECT Job_Name
FROM #Job_Names

SET @job_id = NULL

OPEN disable_jobs
FETCH NEXT FROM disable_jobs INTO @job_name

WHILE @@FETCH_STATUS = 0
BEGIN

 EXEC msdb.dbo.sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT

 EXEC msdb.dbo.sp_update_job @job_id, @enabled = 0
 
 SET @job_id = NULL

 FETCH NEXT FROM disable_jobs INTO @job_name

END

CLOSE disable_jobs
DEALLOCATE disable_jobs

DROP TABLE #Job_Names

RETURN

Monday, June 9, 2014 - 12:12:29 PM - samuel Back To Top (32157)

I found this blog which give a better/proper way to disable SQL jobs

 

http://strictlysql.blogspot.com/2010/04/job-still-runs-inspite-of-disabling.html

 

Monday, October 14, 2013 - 10:30:13 AM - Stefano Gioia Back To Top (27142)

Jeremy, not being rude but - could you edit this blog post? It is wrong (the cache needs to be updated) and jobs should be disabled/enabled only by using sp_update_job. Thanks.

Sunday, July 14, 2013 - 1:23:21 PM - Jeremy Kadlec Back To Top (25830)

Paul,

Thank you for the code contribution to the community!

Thank you,
Jeremy Kadlec
Community Co-Leader

Saturday, July 13, 2013 - 4:01:14 PM - Paul Back To Top (25825)

Sorry the cut and paste was cut short...

Declare @jobs Table (id int Identity(1,1), jobid uniqueidentifier)
Declare @start int,@jobid uniqueidentifier

Declare @start int,@jobid uniqueidentifier

--Disable only jobs that are enabled

Insert into @jobs
Select Job_id
from MSDB.dbo.sysjobs
Where enabled=1

--Select * from @jobs --For testing only

Set @start=(select MAX(id) from @jobs)


While @start >=1
Begin
Set @jobid=(select jobid from @jobs
Where id=@start)

UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
Where job_id=@jobid


Set @start=@start-1
End

--Select * from @jobs --For testing only

 

--Do your SQL statement here--

 

--Re-Enable only the jobs you disabled

Set @start=(select MAX(id) from @jobs)
While @start >=1
Begin
Set @jobid=(select jobid from @jobs
Where id=@start)

UPDATE MSDB.dbo.sysjobs
SET Enabled = 1
Where job_id=@jobid
Set @start=@start-1
End

 

-Paul

Saturday, July 13, 2013 - 3:37:06 PM - Paul Back To Top (25823)

This was a great post thanks! I took it one step further as I have to disable replication jobs while I do a bulk update to obfuscate data in a replicated table. Furthurmore I had to send this script to a client and had no idea what job names they had running for replication. This did the trick:

 

Declare @start int,@jobid uniqueidentifier

--Disable only jobs that are enabled

Insert into @jobs
Select Job_id
from MSDB.dbo.sysjobs
Where enabled=1

--Select * from @jobs --For testing only

Set @start=(select MAX(id) from @jobs)


While @start >=1
Begin
Set @jobid=(select jobid from @jobs
Where id=@start)

UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
Where job_id=@jobid


Set @start=@start-1
End

--Select * from @jobs --For testing only

 

--Do your SQL statement here--

 

--Re-Enable only the jobs you disabled

Set @start=(select MAX(id) from @jobs)
While @start >=1
Begin
Set @jobid=(select jobid from @jobs
Where id=@start)

UPDATE MSDB.dbo.sysjobs
SET Enabled = 1
Where job_id=@jobid
Set @start=@start-1
End

 

Hope this helps sombody with the same problem

-Paul

Monday, February 18, 2013 - 11:12:19 PM - adam Back To Top (22249)

I'm happier using sp_update_job, rather than updating sysjobs directly, mainly to get around the caching issue. Just stick the query logic from above in a cursor first, eg, for "Disable Jobs By Job Category":

DECLARE @SelectedJobId uniqueidentifier

DECLARE Job_Cursor CURSOR FOR
SELECT Job_Id FROM MSDB.dbo.sysjobs J
INNER JOIN MSDB.dbo.syscategories C
ON J.category_id = C.category_id
WHERE C.Name = 'Log Shipping'

OPEN Job_Cursor
FETCH NEXT FROM Job_Cursor
INTO @SelectedJobId

WHILE @@FETCH_STATUS = 0
BEGIN
  EXEC dbo.sp_update_job @job_id = @SelectedJobId
                        ,@enabled = 0
  FETCH NEXT FROM Job_Cursor
  INTO @SelectedJobId
END
CLOSE Job_Cursor
DEALLOCATE Job_Cursor

Monday, January 7, 2013 - 7:37:31 AM - Jeremy Kadlec Back To Top (21293)

hanumanthareddy,

I think this is the portion of the error message you need to focus on from your DTEXEC command:

Failed to decrypt protected XML node "SQLPassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. 

HTH.

Thank you,
Jeremy Kadlec
Community Co-Leader

Thursday, January 3, 2013 - 5:50:46 AM - hanumanthareddy Back To Top (21246)

Sql jobs are not working under sql server agent in sql server 2008

 

I got below error like these

Job_Name=Km_Report

 

Date                      1/2/2013 11:45:00 PM

Log                         Job History (KM_Report)

 

Step ID                 0

Server                   CATOM-GMAPDVW69

Job Name                            KM_Report

Step Name                         (Job outcome)

Duration                              00:00:39

Sql Severity                        0

Sql Message ID                 0

Operator Emailed                           

Operator Net sent                          

Operator Paged                               

Retries Attempted                          0

 

Message

The job failed.  The Job was invoked by Schedule 13 (KM_Report).  The last step to run was step 1 (KM_Report).

 

 

2nd

 

Date                      1/2/2013 11:45:01 PM

Log                         Job History (KM_Report)

 

Step ID                 1

Server                   CATOM-GMAPDVW69

Job Name                            KM_Report

Step Name                         KM_Report

Duration                              00:00:38

Sql Severity                        0

Sql Message ID                 0

Operator Emailed                           

Operator Net sent                          

Operator Paged                               

Retries Attempted                          0

 

Message

Executed as user: CATOM-GMAPDVW69\SYSTEM. 016     Source:       Description: Failed to decrypt protected XML node "SQLPassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:45:01 PM  Finished: 11:45:39 PM  Elapsed:  38.25 seconds.  The package execution failed.  The step failed.

 

 

 

Wednesday, June 6, 2012 - 8:37:23 PM - Muhammad Sharjeel Ahsan Back To Top (17819)

Great Job

Friday, September 9, 2011 - 5:00:28 AM - Tarni Back To Top (14624)

hi,

i have faced the same problem and found that updating the system table sysjobs doesnt work.it the jobs run.

even though i disabled the corresponding shedule from sysshedule the job still runs. then i restarted the Agent.

and then job was actually disabled.

 

but this is not the case with sp_update_job it disables the job without restarting the agent.

 

Thanks

Thursday, March 24, 2011 - 11:49:40 AM - lmaire Back To Top (13310)


Hi,

I have tried to use the mentionned method to enable/disable a job using T-SQL, and I was facing the same behaviour as sgr mentionned here before (under SQL2000 server): although the status was correctly updated in hte job list, the job was actually acting keeping its previous status (set manually).

I found the solution (at least for me it's working) :
instead of updating the sysjobs table :

UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE [Name] = 'My_Job_Name_xyz';

you should rather use the stored procedure "sp_update_job" :

EXEC msdb.dbo.sp_update_job @job_name = 'My_Job_Name_xyz',
@enabled = 0

Hope it helps...

Laurent.

Tuesday, February 2, 2010 - 1:26:15 PM - littlefuzz Back To Top (4839)
The reason a direct update of the Enabled field doesn't work is that SQL Agent has cached this information and won't see the change until it is restarted. (Taken from here: http://www.sqlservercentral.com/Forums/Topic816271-146-1.aspx)

Tuesday, November 10, 2009 - 5:49:44 PM - pshashi Back To Top (4405)
hi, I have the exact same problem that SGR is describing. I have tested the below script many times. The jobs are showing as they were disabled in SQLAgent, after executing this script BUT the backups are running as usal. USE MSDB; GO UPDATE MSDB.dbo.sysjobs SET Enabled = 0 WHERE [Name] LIKE 'LSBackup%'; GO Is there anything I'm missing?? hi SGR, Jeremy, did you resolve the issue? thanks

Tuesday, March 3, 2009 - 12:58:24 PM - sgr Back To Top (2920)

I tried using this method (changing sysjobs.enabled from 1 to 0) for disabling a group of jobs which contained specific text using the like predicate (SQL 2K5 SP4 Enterprise x64). However, the jobs still ran. In SSMS under Jobs they show disabled (little red arrow on job), on job activity monitor they show disabled and on job properties they show disabled. The schedule is still enabled but I tested this idea by right clicking on each job and selecting disable [one at a time...ugh] and it only seems to disable the job not the schedule.

Is the only way to disable a job using TSQL by using sp_update_job? I don't like this concept because I cannot disable a group of jobs at one a time easily.

 Thank you!

Friday, January 18, 2008 - 3:06:44 PM - admin Back To Top (229)

To the MSSQLTips.com community, 

As an update to this tip, our team has observed some issues with SQL Server 2000 SP4 with respect to determining the 'Next Run Date' without restarting the SQL Server Agent Service.  Please note this potential issue and test accordingly.

Thank you,
The MSSQLTips.com Team














get free sql tips
agree to terms