Learn more about SQL Server tools

   
   








Learn more about SQL Server tools








Learn more about SQL Server tools


Disabling or Enabling SQL Server Agent Jobs

MSSQLTips author Jeremy Kadlec By:   |   Read Comments (15)   |   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: 12/18/2007


About the author
MSSQLTips author Jeremy Kadlec
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Monday, June 09, 2014 - 12:12:29 PM - samuel Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

Great Job


Friday, September 09, 2011 - 5:00:28 AM - Tarni Read The Tip

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 Read The Tip


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 Read The Tip
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 Read The Tip
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 Read The Tip

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 Read The Tip

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




 

Sponsor Information