If you have a large number of SQL Server instances with numerical server names, use this script to add a schedule to a job on all the servers that staggers when the job runs.

If your SQL servers are called ‘Server001’, ‘Server002’, ‘Server003’ etc. and you have a job that you want to run on them all (like database integrity checks, reindexing etc.) but you want to stagger the jobs so they don’t all run at the same time, you can use this script to generate a time value from the server name.

The script will create a schedule to run the job once each Sunday, amend as you require!

USE [msdb]
GO
DECLARE @JobToAddScheduleTo VARCHAR(200)
DECLARE @RunTime INT
DECLARE @StaggerOffset INT
DECLARE @FirstRunTime TIME
DECLARE @NumNumericalDigitsInServerName TINYINT

SET @JobToAddScheduleTo = 'DatabaseIntegrityCheck - USER_DATABASES' --Name of the job
SET @StaggerOffset = 30 --Minutes, change the DATEADD interval to use hours / seconds if required.
SET @FirstRunTime = '01:00:00' --Time you want the first server to run the job at
SET @NumNumericalDigitsInServerName = 3 --Number of numerical digits in the name, e.g.: if name is Server001, this would be 3

SET @RunTime = REPLACE(CONVERT(VARCHAR, DATEADD(MINUTE, CAST(SUBSTRING(@@Servername, LEN(@@SERVERNAME)-1, @NumNumericalDigitsInServerName) AS INT) * @StaggerOffset, @FirstRunTime), 24), ':', '')

DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=@JobToAddScheduleTo, @name=N'Weekly',
@enabled=1,
@freq_type=8,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20180412,
@active_end_date=99991231,
@active_start_time=@RunTime,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO