Script to Disable Multiple Jobs in a SQL Server

Using below script we can generate the SP scripts to disable multiple jobs on a SQL Server

Method 1:
Below script will list all the Active Jobs configured on the SQL Server, we can select the output of [Script_To_Disable_Job] column of the jobs of our interest (which we wanted to disable) and execute the same to disable the job.

 

select

'EXEC msdb.dbo.sp_update_job @job_name='''+name+''''+', @enabled=0' [Script_To_Disable_Job]
,name,[enabled]
from msdb.dbo.sysjobs
where [enabled]=1

Below screen shots shows how we disabled few jobs using above script.





Method 2:

We can disable all the Active Jobs on a SQL Server by executing the below query on that server.

Note: Cross verify before executing the script as it disables all the Active Jobs in single shot.
 

use msdb

set nocount on

declare @maxjobcnt int,@jobname2dis varchar(260),@wcnt int=1
select @maxjobcnt=count(*) from msdb.dbo.sysjobs
where [enabled]=1

 

declare @tbl_disablejobs table(cnt int identity(1,1),

name varchar(260),[enabled] tinyint)

insert @tbl_disablejobs (name,[enabled])
select name,[enabled]
from msdb.dbo.sysjobs
where [enabled]=1
--select * from @tbl_disablejobs

 

while (@wcnt<=@maxjobcnt)

Begin

select @jobname2dis=name from @tbl_disablejobs
where cnt=@wcnt

--select @jobname2dis

EXEC msdb.dbo.sp_update_job @job_name=@jobname2dis, @enabled=0
Print '['+@jobname2dis+'] Job is Disabled'
set @wcnt=@wcnt+1
End

set nocount off