Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2010
    Posts
    75

    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!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    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.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •