Monday 17 August 2009

Changing timings of SQL server agent jobs on mass

This is an example of how to change the timing of a series of jobs on the SQL Server agent - in this instance we needed to change all of our full backups to run every third day (well Monday then Thursday, Tuesday then Friday and Wednesday and Sunday leaving Saturday for other maintenance activities).
-- Routine to set the times of all jobs named "Full backup%" on a machine, splitting the jobs into 3 batches, Mon&thur, Tue & Fri and Wed & Sun
-- log backups should be occuring the rest of the time which leaves saturday for maintenance work
-- output is a series of statements which when executed to text output can be copied and executed in a new query window.
set nocount on
create table results (dsp varchar(500))
declare cJobs cursor fast_forward for
select schedule_uid from msdb..sysschedules where name like 'Full Backup%' order by name, schedule_uid

declare @UID varchar(128)
declare @count int
declare @MT int
declare @TF int
declare @WS int
declare @MTStart int
declare @TFStart int
declare @WSStart int

set @MT = 0
set @TF = 0
set @WS = 0
set @MTStart = 40000
set @TFStart = 40000
set @WSStart = 40000

set @count = 1
open cJobs
fetch next from cJobs into @UID
while @@fetch_status = 0
begin
if @count%3 = 0
begin
set @MT = @MT+1 -- Mon & Thur = freq_interval = 18
insert into results select 'update msdb..sysschedules set freq_interval = 18, active_start_time='+cast(@MTStart as varchar)+' where schedule_uid = '''+@UID+''''
set @MTStart = @MTStart + 200
if @MTStart = 46000 begin
set @MTStart = 50000
end else if @MTStart = 56000 begin
set @MTStart = 60000
end else if @MTStart = 66000 begin
set @MTStart = 70000
end else if @MTStart = 76000 begin
set @MTStart = 80000
end
end else if @count%2 = 0 begin
set @TF = @TF+1 -- Tue & Fri = 36
insert into results select 'update msdb..sysschedules set freq_interval = 36, active_start_time='+cast(@TFStart as varchar)+' where schedule_uid = '''+@UID+''''
set @TFStart = @TFStart + 200
if @TFStart = 46000 begin
set @TFStart = 50000
end else if @TFStart = 56000 begin
set @TFStart = 60000
end else if @TFStart = 66000 begin
set @TFStart = 70000
end else if @TFStart = 76000 begin
set @TFStart = 80000
end
end else begin
set @WS = @WS+1 -- Wed & Sun = 9
insert into results select 'update msdb..sysschedules set freq_interval = 9, active_start_time='+cast(@WSStart as varchar)+' where schedule_uid = '''+@UID+''''
set @WSStart = @WSStart + 200
if @WSStart = 46000 begin
set @WSStart = 50000
end else if @WSStart = 56000 begin
set @WSStart = 60000
end else if @WSStart = 66000 begin
set @WSStart = 70000
end else if @WSStart = 76000 begin
set @WSStart = 80000
end
end
fetch next from cJobs into @UID
set @count = @count +1
end
close cJobs
deallocate cJobs
print 'Mon Tue:'+cast(@MT as varchar) +', Tue Fri: '+cast(@TF as varchar)+'Wed Sun : '+ cast(@WS as varchar)
select * from results order by 1
drop table results

Update : we have discovered that the table sysjobschedules hold start times too but cycling the agent should bring everything back into step.