5

I need to test SQL Agent over a period of time, so need to change the system clock into the future quite often (and back)... don't worry it's only my local machine not a server :-)

The trouble is when I set it back again to the present date time, the next run date still ad-hears to the future times.

e.g. If I've put my clock into the future and the job is set to run every minute and it was last run in 11/01/2080 01:01:00 then it's next run is 11/01/2080 01:02:00, even if I set the clock back to the present.

It is understandable that this is the normal behaviour of SQL Server. Is there a way of resetting the next run date easily?

I've tried running the job manually but it has no effect on the next run time.

3
  • Hi @AaronBertrand I must admit I wasn't counting. But looking at my Last Run date (from when I last ran it manually) it's been about 40 minutes, so looks like it didn't fix itself. Jan 11, 2013 at 16:54
  • Is this a situation where a database snapshot of msdb might be useful? If you have enterprise. Jan 11, 2013 at 17:11
  • My research was on 2008. Since you're still on 2005, the frequency of the background job might be different (or not present at all - maybe an unpublicized bug). Not that they're going to have a lot of interest in fixing a bug on an old version, and for a scenario that isn't exactly a common production issue. Jan 11, 2013 at 17:41

2 Answers 2

6

After the good work done by Aaron, I found that executing the result from my ReloadJobSchedules column below did the trick. For some reason I couldn't get the schedule to refresh to the correct next run date by manually updating msdb.dbo.sys.sysjobschedules.next_run_date .

SELECT  j.name AS JobName
        ,sch.name AS ScheduleName
        ,'EXECUTE msdb..sp_update_jobschedule @job_name = ''' + j.name + ''', @name = ''' + sch.name + '''' AS ReloadJobSchedules
FROM    msdb.dbo.sysjobschedules s
INNER JOIN
        msdb.dbo.sysschedules sch
        ON  s.schedule_id = sch.schedule_id
INNER JOIN
        msdb.dbo.sysjobs j
        ON s.job_id = j.job_id
WHERE   sch.[enabled] = 1       
ORDER BY
        j.name
2
  • 1
    The answer by Pixelated worked for me. We had maintenance to virtual servers that got our clocks out of sync and the agents literally didn't know what time it was. Your script got them reset without having to restart production servers. Great thanks, this is an awesome resource!
    – user28338
    Sep 16, 2013 at 16:13
  • Many thanks for the feedback Bob its much appreciated. Always nice to know that you've helped a fellow DBA out of what sounded like a production bind!
    – Pixelated
    Sep 16, 2013 at 21:58
5

Well, you can possibly update msdb..sysjobschedules manually if you don't want to wait 20 minutes for it to correct itself by the background thread (as described in this answer).

UPDATE msdb.dbo.sysjobschedules
  SET next_run_date = CONVERT(INT, CONVERT(CHAR(8), GETDATE(), 112))
  WHERE CONVERT(CHAR(4), next_run_date) > '2020';

However, I haven't tried this, so use with caution.

2
  • Thanks for the idea, shame next_run_date isn't datetime. Jan 11, 2013 at 16:57
  • @AlexKey that has been a point of contention and frustration since I started my career in SQL Server. Try running date-based aggregation queries against sysjobhistory - yuck. Jan 11, 2013 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.