Enabling & Disabling all SQL Agent Jobs

As a DBA you may sometime be asked to disable all jobs on SQL server due to upgrade etc…

Thus, how can you go about disabling the jobs, well you could do this manually or even stop the SQL Server agent.

However, there’s always the reboot which could cause SQL Server Agent to run again if in automatic start up mode and your jobs would run if not disabled. Also, if there are many SQL Agent jobs then it’s going to take you time to disable each job manually and enabling.

This blog shows how disabling or enabling all SQL Agent Jobs can be achieved using TSQL:

• Functions

• Looping

• Dynamic Management Views

• Temporary tables

• DML (Data Manipulation Language)

• DDL (data definition language)

 

— Enabling & Disabling all SQL Agent Jobs

— Author: Dharam Patel

— Creates Temp table to store the required information (Not required, just an example of a temporary table DDL) —–

USE [msdb]

GO

create table #sqljobinfo

( count_id int identity(1,1),

job_id nvarchar(255)

)

— Selects and inputs data into the temp table (Create the temp table)———

select identity(int,1,1) as count_id, job_id, name into #sqljobinfo from msdb.dbo.sysjobs;

— select all results from temp table —

select * from #sqljobinfo

drop table #sqljobinfo (Use at end to drop the temp table)

–Counts the number of rows in sysjobs using the count(*) function —

declare @Total as int

select @Total = count(*) from msdb.dbo.sysjobs

— Counter variable for increment looping —

declare @counter as int

set @counter = 1

— Check condition—

while (@counter <=@Total)

begin

declare @jobid as nvarchar(255)

declare @Jobname as nvarchar(255)

set @jobid = ”

set @Jobname = ”

select top 1 @jobid = job_id from #sqljobinfo where #sqljobinfo.count_id = @counter

select top 1 @Jobname = name from #sqljobinfo where #sqljobinfo.count_id = @counter

— 1 for enable & 0 for disable –

–Can use either @job_name or @job_id for the sp_update_job check conditions –

EXEC msdb.dbo.sp_update_job @job_id=@jobid,@enabled = 0

— ‘Counter value being incremented’–

SET @counter = @counter + 1

end

Please share your thoughts on any improvements or other ideas to achieve this.

Leave a Reply

avatar
  Subscribe  
Notify of