Reset / force SQL Agent Next run date

Question

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.

Answer 1

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

Answer 2

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.