Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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. –  Alex Key Jan 11 '13 at 16:54
    
Is this a situation where a database snapshot of msdb might be useful? If you have enterprise. –  Alan Whited Jan 11 '13 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. –  Aaron Bertrand Jan 11 '13 at 17:41
add comment

2 Answers

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.

share|improve this answer
    
Thanks for the idea, shame next_run_date isn't datetime. –  Alex Key Jan 11 '13 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. –  Aaron Bertrand Jan 11 '13 at 17:05
add comment

After the good work done by Aaron above 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
share|improve this answer
    
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 '13 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 '13 at 21:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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