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

Alex, George 31 Reputation points
2020-12-18T23:29:18.623+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,437 questions
{count} votes

Accepted answer
  1. Tom Phillips 17,741 Reputation points
    2020-12-21T13:55:53.983+00:00

    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.


6 additional answers

Sort by: Most helpful
  1. Will Spurgeon 0 Reputation points
    2024-12-20T17:38:57.26+00:00

    You can run the following to generate a set of innocuous updates that will cause the job schedules to recalculate. This is just setting the "active_start_date" of the job to it's existing value, but also triggers the recalculate. I had to do this b/c the SQL Agent restart was insufficient.

    SELECT schedule_id

    , name

    , active_start_date

    , N'EXEC msdb.dbo.sp_update_schedule @schedule_id = ' + CAST(schedule_id AS NVARCHAR) + ', @active_start_date = '

    + CAST(active_start_date AS NVARCHAR) + ';' AS sqlToRun

    FROM msdb.dbo.sysschedules

    0 comments No comments

  2. Will Spurgeon 0 Reputation points
    2024-12-20T17:41:20.6966667+00:00

    You can run the following to generate a set of innocuous updates that will cause the job schedules to recalculate. This is just setting the "active_start_date" of the job to it's existing value, but also triggers the recalculate. I had to do this b/c the SQL Agent restart was insufficient.

    SELECT schedule_id

    , name

    , active_start_date

    , N'EXEC msdb.dbo.sp_update_schedule @schedule_id = ' + CAST(schedule_id AS NVARCHAR) + ', @active_start_date = '

    + CAST(active_start_date AS NVARCHAR) + ';' AS sqlToRun

    FROM msdb.dbo.sysschedules

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.