How to disable SQL Server Agent jobs using single script?
-
Hey guys,
I also have same problem.This is regarding sql 2005 std ed.
I want to disable all sqlserver agent jobs with checking the status. If job is running, the script will just say a job is running and if jobs are idle, I want to disable all jobs by single script either by VBS script or t-sql script. This is related to planned maintenance of Logshipping environment.
Let me help guys.....
Thanks
AKP
-
Scott's script should help you out.
-
This is the sproc we are using.
It also takes care of alerts bound to a job !
This proc is always installed when we install an instance !
Basically what we do is :
1) disable the job matching the given pattern
2) at the same time we RENAME the jobs, so everybody knows why it has been disabled ( prefix provided by sproc executor )
3) Record and Disable all alerts that have pattern matching jobs as action.
4) off course there is also a spc_DBA_Enable_Jobs to revert this action.
Create procedure spc_DBA_Disable_Jobs@HotRunNY char(1) = 'N'
, @NameLike varchar(120)
, @DisabledPrefix varchar(50) = 'DBADisabled_'
, @tsStop datetime OUTPUT
, @AlertsDone int OUTPUT
, @JobsDone int OUTPUT
as
-- ALZDBAdd 06/09/2004
-- Disable jobs en alerts ivm MFMigratie 2004
-- -- how to execute
-- DECLARE @rc int
-- DECLARE @HotRunNY char(1)
-- DECLARE @NameLike varchar(120)
-- DECLARE @DisabledPrefix varchar(50)
-- DECLARE @tsStop datetime
-- DECLARE @AlertsDone int
-- DECLARE @JobsDone int
--
-- Select @HotRunNY ='Y'
-- , @NameLike 'P[_]'
-- , @DisabledPrefix = 'voorbeeldOPKUIS_MES_'
--
-- -- Set parameter values
-- EXEC @rc = [dbo].[spc_DBA_Disable_Jobs] @HotRunNY = @HotRunNY , @NameLike = @NameLike, @DisabledPrefix = @DisabledPrefix , @tsStop=@tsStop OUTPUT , @AlertsDone=@AlertsDone OUTPUT , @JobsDone=@JobsDone OUTPUT
--
-- Select @rc as RC
-- , convert(varchar(25), @tsStop, 121) as tsStop
-- , @AlertsDone as AlertsDone
-- , @JobsDone as JobsDone
set nocount on
-- Declare @HotRunNY char(1)
-- set @HotRunNY = 'N' -- Aanpassen N = test, Y = hotrun (productie)
-- Declare @NameLike as varchar(120)
-- set @NameLike = 'DB2' -- deel van de jobnaam
-- declare @tsStop as datetime
-- verwerking
if @DisabledPrefix = 'DBADisabled_'
begin
declare @userid varchar(100)
set @userid = suser_sname()
set @DisabledPrefix = substring(@UserId, CHARINDEX ( '\' , @userid, 1) + 1 , datalength(@UserId)) + '_Disabled_'
end
declare @dtRun as datetime
declare @sql varchar(1000)
declare @JobName varchar(128)
DECLARE @Tellerke integer
declare @ix integer
declare @Melding varchar(100)
select @tsStop = getdate()
, @AlertsDone = NULL
, @JobsDone = NULL
, @dtRun = convert(datetime, convert(char(10),getdate(), 121) + ' 00:00:00.000')
, @Melding = case @HotRunNY
when 'Y' then '*** Hot - Hot - Hot ***'
else '--- TEST - TEST - TEST ---'
end
IF @HotRunNY <> 'Y'
begin
Print @Melding + char(13) + @Melding + char(13) + @Melding + char(13) + @Melding + char(13) + char(13)
end
-- -- Schedules (niet nodig want als de job disabled is , doet het schedule niets !!
-- Launching via Alerts
set @Tellerke = 0
-- drop niet nodig want tsStop is opgenomen !!! drop table [msdb].[dbo].[T_DBA_Disabled_Alerts]
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_DBA_Disabled_Alerts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
CREATE TABLE [msdb].[dbo].[T_DBA_Disabled_Alerts] (
[Alertname] [sysname] NOT NULL ,
[Jobname] [sysname] NOT NULL ,
[dtRun]datetime not null,
[tsStop] datetime not null,
[UserStop] varchar(50) not null default suser_sname(),
[tsStart] datetime not NULL,
[UserStart] varchar(50) not NULL default ''
) ON [PRIMARY]
end
begin transaction
insert into msdb.dbo.T_DBA_Disabled_Alerts (Alertname, Jobname, dtRun, tsStop,tsStart)
Select A.name as Alertname, J.name as Jobname, @dtRun, @tsStop, @dtRun
from msdb.dbo.sysjobs J
inner join msdb.dbo.sysalerts A
on J.job_id = A.job_id
and J.name like '%'+@NameLike+'%'
and J.enabled = 1
and A.enabled = 1
order by A.name, J.name
declare csrAlertSchedules cursor for
select ' msdb.dbo.sp_update_alert @name = N''' + Alertname + ''' ,@enabled = 0', Jobname
from msdb.dbo.T_DBA_Disabled_Alerts
where dtRun = @dtRun
and tsStop = @tsStop
and tsStart = dtRun -- = default waarde
order by Alertname
for read only
open csrAlertSchedules
FETCH NEXT FROM csrAlertSchedules
INTO @sql, @JobName
WHILE @@FETCH_STATUS = 0
BEGIN
if @HotRunNY = 'Y'
begin
exec (@SQL)
end
else
begin
print @sql + ' -- [' + @JobName + ']'
end
set @Tellerke = @Tellerke + 1
-- Volgende rij inlezen
FETCH NEXT FROM csrAlertSchedules
INTO @sql, @JobName
END
set @AlertsDone = @Tellerke
IF @HotRunNY <> 'Y'
begin
print 'aantal Alerts verwerkt : ' + cast(@Tellerke as varchar) + ' **'
Print char(13) + @Melding + char(13) + @Melding + char(13) + @Melding + char(13) + @Melding + char(13) + char(13)
end
-- Cursor afsluiten
CLOSE csrAlertSchedules
DEALLOCATE csrAlertSchedules
-- 'MFMigratie_Uitgeschakeld - '
-- Jobs
set @Tellerke = 0
declare csrJobs cursor for
select ' msdb.dbo.sp_update_job @job_name = N'''+name+''', @new_name = N''' + @DisabledPrefix + name +''', @enabled = 0'
from msdb.dbo.sysjobs
where name like '%'+@NameLike+'%'
and enabled = 1
order by name
for read only
open csrJobs
FETCH NEXT FROM csrJobs
INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
if @HotRunNY = 'Y'
begin
exec (@SQL)
end
else
begin
print @sql
end
set @Tellerke = @Tellerke + 1
-- Volgende rij inlezen
FETCH NEXT FROM csrJobs
INTO @sql
END
set @JobsDone = @Tellerke
IF @HotRunNY <> 'Y'
begin
print '# Jobs processed : ' + cast(@Tellerke as varchar) + ' **'
Print char(13) + @Melding + char(13) + @Melding + char(13) + @Melding + char(13) + @Melding + char(13) + char(13)
end
-- Cursor afsluiten
CLOSE csrJobs
DEALLOCATE csrJobs
-- Select * from msdb.dbo.T_DBA_Disabled_Alerts
commit transaction
go
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply