Tuesday, 26 March 2013

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.
'EXEC msdb.dbo.sp_update_job @job_name='''+name+''''+', @enabled=0' [Script_To_Disable_Job]
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)

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

set nocount off


1 comment:

  1. Nice news and thanks for sharing this here to let me know this updates,please know the recent updates by visiting this link also…because I had gain some info from here also: SQL Server Jobs