Enterprise Manager Job Scheduler and the Time change problem

We just noticed a problem with the SQL Server Agent's Job Scheduler. During
the time change the other week, the system clock would reset itself every
day. I've seen it go an hour ahead and an hour behind. During this time,
some of the jobs got out of synch.
Let's say we have a few jobs that run every 4 hours. one of the clock
changes happened in the hour before it was to run, so afterward, the current
time is now passed when the job was scheduled to run. Since the time for
it's next scheduled run has already passed, the job just sits there and never
runs again.
Why does SQL Server not see that a jobs scheduled time has passed, and just
continue with the next run in the schedule?Hi
"Granola Pete" wrote:
> We just noticed a problem with the SQL Server Agent's Job Scheduler. During
> the time change the other week, the system clock would reset itself every
> day. I've seen it go an hour ahead and an hour behind. During this time,
> some of the jobs got out of synch.
> Let's say we have a few jobs that run every 4 hours. one of the clock
> changes happened in the hour before it was to run, so afterward, the current
> time is now passed when the job was scheduled to run. Since the time for
> it's next scheduled run has already passed, the job just sits there and never
> runs again.
> Why does SQL Server not see that a jobs scheduled time has passed, and just
> continue with the next run in the schedule?
Is the system time being effected and where/how is the time being
synchronised to?
John|||Yes the system clock is what was being affected. From about Monday the 12th
until about Wed 14th or Thurs 15th, the clock would reset itself each morning
to a different time. It is synching itself to "time.windows.com". It has
stabilized itself now.
Having the time switch near the time when a scheduled job is supposed to run
doesn't seem much of a problem.
It is more troubling that the Agent Job Scheduler couldn't recover the
schedule after "missing" a run at a certain time. Granted, I should have
been checking the jobs, but there is usually no need unless I get
notification that an error has occured.
"John Bell" wrote:
> Hi
> "Granola Pete" wrote:
> > We just noticed a problem with the SQL Server Agent's Job Scheduler. During
> > the time change the other week, the system clock would reset itself every
> > day. I've seen it go an hour ahead and an hour behind. During this time,
> > some of the jobs got out of synch.
> >
> > Let's say we have a few jobs that run every 4 hours. one of the clock
> > changes happened in the hour before it was to run, so afterward, the current
> > time is now passed when the job was scheduled to run. Since the time for
> > it's next scheduled run has already passed, the job just sits there and never
> > runs again.
> >
> > Why does SQL Server not see that a jobs scheduled time has passed, and just
> > continue with the next run in the schedule?
> Is the system time being effected and where/how is the time being
> synchronised to?
> John|||Hi
"Granola Pete" wrote:
> Yes the system clock is what was being affected. From about Monday the 12th
> until about Wed 14th or Thurs 15th, the clock would reset itself each morning
> to a different time. It is synching itself to "time.windows.com". It has
> stabilized itself now.
> Having the time switch near the time when a scheduled job is supposed to run
> doesn't seem much of a problem.
> It is more troubling that the Agent Job Scheduler couldn't recover the
> schedule after "missing" a run at a certain time. Granted, I should have
> been checking the jobs, but there is usually no need unless I get
> notification that an error has occured.
>
I am not sure how you are will reliably know that there has been a run
missed because of a time change! There could be numerous scenarios of time
changes that you would have to provide decissions on whether or not the job
should run.
John|||Well, I know that a run has been missed and that it hasn't run again simply
by the fact that the scheduler shows the "Last Run Status(Start Date)" field
shows the 11th and the "Next Run Date" shows the 12th and it is already the
20th, no history of the run since 11th.
the problem isn't so much that it missed it, but that it didn't hit the next
scheduled time to run.
The system doesn't necessarily have to be smart enough to look back and see
when the last time it ran. If it is on a recurring schedule, say every 4
hours, it doesn't need to know that it didn't run at noon, only that it needs
to run at 4 and then 8, etc. but I would hope it is smart enough to know
that if it's "Next Run Date" is somehow an earlier date than the current
time, that it recovers or at least throws an error.
The thing is, if a job fails once, it still runs the next time it is
scheduled to run. How is that any different from missing a run time? It is
just odd to me that it would work in one instance, but not the other.
"John Bell" wrote:
> I am not sure how you are will reliably know that there has been a run
> missed because of a time change! There could be numerous scenarios of time
> changes that you would have to provide decissions on whether or not the job
> should run.
> John