Job next run times don't update

When you change the system clock the SQL Server is running
on the next run times for the jobs do not update, the only
way to get them to update is to stop and restart the agent?Hi David,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with your issue.
First of all, I would like to confirm my understanding of your issue.
From your description, I understand that you would like to know if stop and restart the agent is
the ONLY way to update the jobs' next run times. Have you successfully update the run time
with this method?
Based on my experience, even though you re-start the Agent, the jobs' run times will not be
updated because SQL calls getlocaltime() API of the Operating System to schedule jobs.
What's more, I'm unsure of what causes you changed the system clock, except for the
Daylight Savings Time Change. If the "Daylight Savings Time Change" is not the scenario on
your side. Could you specify more on the scheduled jobs and how you change the system
clock so that we will be clearer what happened on your side. Thanks in advance!
During a Daylight Savings Time change, there is no need to stop the SQL Server services
since it does not utilize the current date and time for any critical functionality that would result in
data integrity issues.
"SQL Server" scheduled jobs to handle daylight saving's time with the following two methods
to track job schedules:
1. Scheduling can be done on a polled basis, scanning all jobs for the time they should be run,
e.g. daily at 1:05am. This would find and run the job a second time when the clock is set back
to 1am.
2. Scheduling can be done on a queued basis, where jobs are queued according to the next
time they should be run and dequeued once they are executed at that time (and enqueued at
their new next time). This way they will only be run a single time, the first time the date/time is
encountered.
"SQL Server Agent" uses the SECOND queued method for its job scheduling. It uses the
sysjobschedules table which has the next_run_date and next_run_time columns which get
updated periodically by SQL Server Agent.
Since the next run time is reset every time a job is run, the job will not be run multiple times if the
clock is set back. This is fine for jobs that run only once a day or less often. It is probably what
you expect will happen; the job will not be run a second time.
However, if you have a job that executes once an hour or more frequently, you probably do
want it to continue to run on the same schedule even if the clock has been turned back.
Perhaps your job cleans out a table every 5 minutes :-) ; you'd expect it to clean out the table
every 5 minutes regardless of whether the clock has been set back.
But with SQL Server, when the job is run at 1:58am (for example), the next run time will be set to
2:03, which now will not happen for over an hour once the clock is set back to 1:00am. And of
course, in the Spring when the clocks are moved forward, there is a different problem where
the times between 2am and 3am never occur, probably skipping any jobs that were
scheduled during that time. I haven't tested this.
David, does that answer your question? If there is anything more I can assist you with, please
feel free to post it in the group.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.