Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Other SQL Server Topics (2005)  SQL script to automatically distribute utility job

Author  Topic  rbecl Starting Member 1 Post Posted - 2010-08-16 : 05:40:25 Hi, I would like to write a SQL script to automatically distribute all the utility jobs over a 19:00 to 21:00 time window becuase we moved a lot of databases and, because of the way they were moved, all their utility jobs are now scheduled for 19:45. These should be spread out between 19:00 and 21:00, so jobs scheduled for 19:45 had to be re-scheduled manually then so that they don’t all happen at once. So far, I have: --Check times of existing backup jobs use MSDB select [name], next_run_date, next_run_time from sysjobschedules js left join sysjobs j on j.job_id = js.job_id where [name] like '%Utility01%' --and [next_run_time] = 194500 order by next_run_date, next_run_time -- get the number of jobs use msdb DECLARE @count int, @time int, @incrementPeriod int; SELECT @count = COUNT(job_id) from [sysjobschedules] where [next_run_time] between 190000 and 210000 SELECT @count -- find average space of time period distribution SET @time = 110; SET @incrementPeriod = @count/@time SELECT @incrementPeriod -- Does anyone have any advice as to how I can distribute the their new times? any help is appreciated