Job that should run every few minutes scheduled for future date

Other SQL Server Topics
1

This is for a test server running SQL Server 2008.

We have a task scheduled through SQL Server Agent that's supposed to run every few minutes. Apparently, somebody a few months ago did a test where they changed the server date to the future, then changed it back. So we have record of things running on August 30, 2015, even though that's not until next week.

We cleared out the log history and data that we know of with the future dates, but for some reason, our scheduled job claims that it's not scheduled to run again until August 30, even though I just ran it manually, and its schedule in its properties says it should be running every few minutes. Any idea what I'm missing that would cause it to retain that August 30 scheduled date and ignore its "every few minutes" scheduling?

2

I don't think you're missing anything. That's likely intended behavior for efficiency, to only check the job when it's necessary. I suggest dropping and recreating the schedule, esp. if that's the only job that uses that schedule.

3

I think it is intended behaviour to - but of a PITA though, I agree.

My understanding is that after the job finishes it sets its "Next Run Date/time" (based on the anniversary of NOW and the job's schedule. i.e. if its runs for long enough to over-run the next scheduled start time it then sets its Next Run Date/time what will be the next scheduled time after that)

So if the clock is set back the job will not run earlier than its Next Run Date/time

I believe this is also an issue for Daylight Saving Time when clock changes back [i.e. in the Autumn] (or it makes a case for not setting Daylight Saving time on a server and leaving it at a non-changing time throughout the year).

We take a lot of trouble to make sure that clocks on our servers stay accurate - my understanding is that once they become wrong by X-minutes then any auto-correct from a TIME service will no longer be applied - and once that happens they can drift a long time, if no one is watching out for them, before the problem gets noticed - and it is then a nightmare to sort it out, particularly if they are replicated to another server also updating records using Server Timestamps :frowning:

4

Thanks. I created a duplicate job, and it's fine. It's annoying, but I guess that's life.

5

If you ran it manually I wonder if it would then re-schedule for next-anniversary-time-after-that?