SQL Server 2019 - Scheduled jobs Bizarre incident - Next run date of all the jobs moved 6 months Forward by itself

Question

We are using SQL Server 2019 Enterprise Edition (Version 15.0.4073.23).

Have about 100+ scheduled jobs which are hourly, daily, weekly or Monthly. Jobs stopped working today and on troubleshooting found that all the jobs are suddenly showing Next run date(Job activity Monitor) of some date in June 2021.

No change has been made to the schedule of these jobs, and everything else looks normal. But the jobs won't run due to this issue.
I have found a way to fix this by updating the schedule of the jobs. Once I do this Next run time is getting fixed

I have been working with SQL server for about 15 years, and has not seen anything like this.

Would like to know if anybody has faced similar issue or any idea what might be causing this.

Answer 1

This happens when the system clock date is set incorrectly when SQL Agent was started. Usually restarting the SQL Agent service will fix this problem. The next job start is recalculated when the agent service starts.

Answer 2

We experienced this same issue.Because of low battery warning on one of the servers we had hard restart and change the battery. After restart the server date changed to 2045 and it took some time until server finished synchronization with microsoft. So this date change effects on all the sql agent and windows task scheduler jobs and also some SSRS report subscriptions.
We tried to restart the sql agent service but it didnt help so we had to write a query to bulk update all the sql jobs and forced them to re save without changing anything.
This fixed the sql jobs issue but for windows task scheduler and SSRS we had to identify them one by one and resave them.
The scary part was there are lots of windows and sql system created jobs that got effected.

Answer 3

Hi @Alex, George ,

SQL server will not change this automatically, may be someone change the schedule of the jobs by mistake. It is hard for us to analysis why the values changed if you did not open the profiler trace for this. But opening the profiler trace to waiting for issue produce is not possible. I test in my environment, when you change the job scheduler, SQL will call the sp_update_schedule to update the settings for a SQL Server Agent schedule, the Next run time will be changed.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.

Answer 4

I recently experienced this issue in my environment. It is caused by the Clock Skew issue described elsewhere.

[https://documentation.red-gate.com/sm/troubleshooting/unexpected-behavior/issues-caused-by-clock-skew

If you are collect Clock skews on servers using Get-SCOMEvent (OperationsManager), you may see the following -

--> Time difference greater than 5min detected on: myServerName - Old TimeStamp: 07/01/2023 08:50:53, New TimeStamp: 24/04/2023 16:48:10 - Totals: 154557 minutes, 2576 hours, 107 days.

This caused the Next Run Date visible in Job Activity Monitor, on all SQL instances running on myServerName to display 24/04/2023. The Job schedules themselves are fine.

To fix the issue with the SQL Agent jobs, run an update as described by Arin V.

I opted to run a benign update, to force the schedule to re-save (and fix itself).
dbo.sp_update_schedule @schedule_id = @id, @active_start_date = '20221230'

Answer 5

Just recreated the jobs from scratch