Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Disabling or Enabling SQL Server Agent Jobs


By:   |   Read Comments (18)   |   Related Tips: More > 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.

From a coding perspective, the following options are available to manage SQL Server Agent Jobs with a bit more flexibility:

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
 

Disable Jobs By Job Name

USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE [Name] LIKE 'Admin%';
GO
 

Disable Jobs By Job Category

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
 

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.
  • 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:

 



Last Update:





About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources





More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Friday, April 01, 2016 - 9:43:22 AM - Simon Evans Back To Top

 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

 

 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

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 09, 2014 - 12:12:29 PM - samuel Back To Top

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

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

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

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

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

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 07, 2013 - 7:37:31 AM - Jeremy Kadlec Back To Top

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 03, 2013 - 5:50:46 AM - hanumanthareddy Back To Top

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 06, 2012 - 8:37:23 PM - Muhammad Sharjeel Ahsan Back To Top

Great Job


Friday, September 09, 2011 - 5:00:28 AM - Tarni Back To Top

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


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 02, 2010 - 1:26:15 PM - littlefuzz Back To Top
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
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 03, 2009 - 12:58:24 PM - sgr Back To Top

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

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


Learn more about SQL Server tools