February 10, 2010 at 1:59 am
Hi everybody,
Let us consider v've a planned downtime b/w 2-3 PM. The jobs which are scheduled b/w that time need to be modified and v've some 2000 jobs. How to change the schedule of all the jobs.
please help me with script.
February 10, 2010 at 5:54 am
Use some of this. You need to throw a query in to look up jobs scheduled in between 2 and 3pm using sysjobs and sysjobschedules (off top of my head).
Then pass each of those jobs through this...(where @nextday is a calculation you have to make to reschedule - you haven't stated when you want them rescheduled to...)
USE msdb
DECLARE @nextday int
SET @nextday = (Year(dateadd(day,1,getdate())) * 10000 +
month(dateadd(day,1,getdate())) * 100 + day(dateadd(day,1,getdate())))
EXEC sp_update_jobschedule @job_name = '<name of job>',
@name = <name of schedule>,
@active_start_date = @nextday
The example above I used for a job I have that runs every 5 minutes, and when it eventually sees its "Go" message it runs and then reschedules itself for the next day. So you will probably also want to set the @active_start_time parameter.
Hope this helps.
February 10, 2010 at 6:25 am
You can get the jobs scheduled in the time window you like with something similar to this:
;WITH schedules AS (
SELECT *, sTime = REPLACE(STR(next_run_time,6),' ','0')
FROM msdb..sysjobschedules
WHERE next_run_date > 0
),
dt_schedules AS (
SELECT *,
run_dt = DATEADD(second, CAST(RIGHT(sTime,2) AS INT ),
DATEADD(minute, CAST(SUBSTRING(sTime, 2,2) AS INT),
DATEADD(hour, CAST(LEFT(sTime,2) AS INT),
CONVERT(DATETIME, STR(next_run_date,8), 112))))
FROM schedules
)
SELECT *
FROM dt_schedules
WHERE run_dt BETWEEN DATEADD(hour, 14, CONVERT(CHAR(8), GETDATE(),112))
AND DATEADD(hour, 15, CONVERT(CHAR(8), GETDATE(),112))
UNION
-- Sub day schedules
SELECT *
FROM dt_schedules
WHERE schedule_id IN (
SELECT schedule_id
FROM msdb.dbo.sysschedules
WHERE freq_subday_type > 1
)
The first part identifies the jobs scheduled with next run date in the time window, the second part selects the schedules with sub day frequency.
-- Gianluca Sartori
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply