Skip to main content
Source Link

I ultimately wrote a stored procedure which I run nightly via SQL Agent.

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'dba_StaggerLogShippingJobs' AND type = 'P') DROP PROCEDURE dba_StaggerLogShippingJobs
GO

CREATE PROCEDURE dbo.dba_StaggerLogShippingJobs
AS
-- This job is intended to be run nightly.
-- It queries the msdb..sysschedules table for jobs that start with 'LSBackupSchedule%'
-- It determines how to space the jobs evenly within a 15 minute window, then calls msdb..sp_update_schedule to set the new @active_start_time. 

SET NOCOUNT ON

declare @logShipEvery int = 900 -- Log ship every 900 seconds or 15 minutes
declare @staggerSeconds int -- number of seconds between jobs
declare @new_active_start_time int -- calculated new start time for a given job
declare @current_active_start_time int -- existing start time for a given job.

-- Some simple variables for use in the loop.
declare @i int = 0
declare @maxId int
declare @schedule_id int

-- table variable in which we store all the current log shipping jobs and their current active_start_time
declare @sqlAgentSchedules table (id int identity (0,1) primary key, schedule_id int not null, current_active_start_time int, new_active_start_time int)

-- Fetch all the LSBackupSchedule jobs into a table variable.
-- Order the query by schedule_id, which monotomically increases as new jobs are added, so that we 
-- can do some tricks to make sure we're not unnecessarily updating schedules nightly
-- when the new value would be equal to the old value.
insert into @sqlAgentSchedules (schedule_id, current_active_start_time)
select schedule_id, active_start_time from msdb..sysschedules 
where name like 'LSBackupSchedule%'
order by schedule_id

select @maxId = (select MAX(id) from @sqlAgentSchedules)

select @staggerSeconds = @logShipEvery / @maxId

-- Calculate a new staggered active_start_time for each job
-- and store it back in our table variable.
-- Formatting the active_start_time is a little tricky because MS is using an integer in a weird
-- way to represent a time value.  For example, the integer value 235959 is actually 23:59:59, or a second before midnight.
-- Took some shortcuts here with the assumption that our time values will always lie between 0 and 15 minutes.
-- In other words, I only bothered to zero-pad the seconds value, and not the minutes value, and I completely
-- ignored the possibility of needing to ever set the hours value.
update @sqlAgentSchedules set
 new_active_start_time = convert(int,convert(varchar(2),@staggerSeconds * id / 60) + right('0'+convert(varchar(2),@staggerSeconds * id % 60),2))

-- Loop over each row of our table variable and update the active_start_time 
-- for any log shipping job that needs to be updated.
while @i <= @maxId
begin
    -- Get the values from this row.
    select 
        @schedule_id = schedule_id, 
        @current_active_start_time = current_active_start_time,
        @new_active_start_time = new_active_start_time
    from @sqlAgentSchedules
    where id = @i

    -- Only update the job schedule if we'd be making a change to its existing active_start_time.
    if @new_active_start_time <> @current_active_start_time
    begin
        exec msdb..sp_update_schedule @schedule_id = @schedule_id, @active_start_time = @new_active_start_time
        print 'Updating schedule for job ' + convert(varchar(8),@schedule_id) + '. Old active_start_time: ' + convert(varchar(8),@current_active_start_time) + '. New active_start_time: ' + convert(varchar(8),@new_active_start_time)
    end
    
    select @i = @i + 1
end

SET NOCOUNT OFF
Post Made Community Wiki by Larry Silverman