Tuesday, March 20, 2012

scheduled jobs and daylight savings time

This must've been convered before, but I can seem to find a solution to it. Daylight Savings Time is about to end in my area, at which time the clocks will go backwards from 1:59am to 1:00am.
I have scheduled jobs that run every minute, and I'm expecting that when the clock rolls back, they will stop running for the gained hour until the clock catches back up.
So, I need an automated way to correct this.

I was looking at the msdb..sysjobschedules table, at the next_run_time column. But it doesn't make much sense. That value will periodically update, but it is always a good bit behind the next run time reported in EM. And manually updating it seems to have no effect.
Is there a way to get a job to run or to recalculate the next run time via tsql script?you can start a job via tsql with a call to msdb.dbo.sp_start_job.

it's got an entry in BOL.

If my memory serves correctly, EM figures out when the job is running next by calling msdb.dbo.sp_help_job (also in BOL). EDIT: you could verify this by turning on profiler and then opening up the job activity UI in EM.|||A googled about a bit, and found an article (http://blog.ragan.com/archives/sqlblog/2004/11/mvp_ron_talmage.html) that seems to explain it all. The next_run_time in the system table is "left hanging" by the time change. The next_run_time is reset to 2:01, and SQL Agent dutifully waits until 2:01 arrives, even it if is an hour away from 2:00. If you get the job to run at the second 1:01 AM, it should execute normally for the rest of the extra hour. The only problem is, getting it to run at that time. I don't suppose you have a server kicking about that does not change it's time automatically?|||Well, I can't seem to get it working.
On a test server I stopped my jobs (they run every minute). I then set the clock back and hour. I waited several minutes and as I expected, the jobs didn't start. So I executed sp_start_job, which did start the job. I expected that when the job completed, it would re-calculate the next-run time, but that didn't happen. So I ran sp_help_job. No change. I then manually updated the next_run_time and set the value to 0, then manually started the job again. Still no change. I even stopped and restarted the SQL Server Agent. Nothing.
I can't seem to get it to recalculate the next-run time. Frustrating.
There must be an easy solution, it seems like it would be a somewhat common problem.|||Found a way to trick it. If I run sp_update_jobschedule and disable and re-enable the schedule, it re-calcs the time and my jobs start back on schedule.

sp_update_jobschedule @.name = 'schedulename', @.job_name = 'jobname', @.enabled = 0
sp_update_jobschedule @.name = 'schedulename', @.job_name = 'jobname', @.enabled = 1

Thanks for the replies so far.|||Just in case anyone needs it, here is a script which forces all the enabled jobs to re-calc their start times.

DECLARE @.jobname varchar(128)
DECLARE @.schedname varchar(128)

DECLARE tableCursor INSENSITIVE CURSOR FOR
SELECT job.name, sched.name
FROM msdb.dbo.sysjobs job, msdb.dbo.sysjobschedules sched
WHERE job.job_id = sched.job_id
AND job.enabled = 1
AND sched.enabled = 1

OPEN tableCursor
IF @.@.CURSOR_ROWS <> 0
BEGIN
FETCH NEXT FROM tableCursor INTO @.jobname, @.schedname

WHILE (@.@.FETCH_STATUS = 0)
BEGIN
-------------
EXEC msdb.dbo.sp_update_jobschedule @.name = @.schedname, @.job_name = @.jobname, @.enabled = 0
EXEC msdb.dbo.sp_update_jobschedule @.name = @.schedname, @.job_name = @.jobname, @.enabled = 1
-------------
FETCH NEXT FROM tableCursor INTO @.jobname, @.schedname
END -- WHILE (@.@.FETCH_STATUS = 0)
END -- IF @.@.CURSOR_ROWS <> 0

CLOSE tableCursor
DEALLOCATE tableCursor

No comments:

Post a Comment