06-28-10, 16:25 #1Registered User
- Join Date
- Feb 2010
Unanswered: Scheduled Jobs break when changing the clock
If I set the system clock into the past, scheduled jobs stop running.
I understand why this is. Internally, SQL says:
ok the last time I ran the job was at x.
right now it is y.
If x + job_interval > y run the job
So for example today is 2010-06-28 and the job runs a few times. Then I set the clock back to 6 months ago, and now the job no longer runs. The job will not run until the clock gets back to 2010-06-28. In other words, the job will not run for 6 months, even though its scheduled to run "every minute".
Ive been googling this problem for a while, and all the responses are along the lines of "well, dont set your clock back".
Its a long story, but I have to set the clock back. I have an application that is simulating the state of things on a particular day back in January. Getting SQL server to co-operate is the last hurtle.
I need some kind of work around, even if its ugly. I have tried stopping and starting the job, but that does not change anything. I have tried manipulating the msdb..sysjobschedules table, but the dates & times in that table seem to be just 'for show' and arnt used by SQL server, so updating them does nothing.
I'm very open to suggestions, thanks!
06-28-10, 16:32 #2Registered User
Provided Answers: 11
- Join Date
- Jan 2003
First thing: Backup the msdb database. Just about anything after this could require a restore from that backup.
The cleanest way to do things would probably be to generate scripts of the jobs, and save those off. After you reset the calendar, drop all the jobs, and recreate them from the scripts.
If you have a lot of jobs, you could try to write a script using sp_update_schedule to update all the schedules to begin at the new start time.
Failing that gets into updating system tables that I would rather leave as a last resort.
06-28-10, 16:50 #3Registered User
- Join Date
- Jul 2003
- San Antonio, TX
Definitely use sp_update_schedule (sp_update_jobschedule if earlier version is being used), and as MC said - change the start time. Backing up and restoring msdb is not the easiest."The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."