Why might Sql Server Agent's Next Run Time value not be what I expect? And is this definitely the next time my job will run?

Question

At times, I'm seeing some inconsistencies with sql server agent's value in the sysjobschedules.next_run_date and time fields.

I have a web interface that interacts with the sql scheduling system.

I've created a test job that started on March 16th 2011 at 9:45am. I've set it up to happen every 4 weeks on a Wednesday.

From today, this should put a next run date and time on January 18th at 9:45 but the Next Run Date/Time value for my job is saying January 4th.

The values in the job properties look good.
The start date says March 16th 2011.
The frequency values are all correct.
But it seems SQL is failing to land this occurrence properly.

Here is some relevant info for the job in question from the sp_help_jobschedule proc

freq_type 8
freq_interval 8
freq_subday_type 1
freq_subday_interval 1
freq_relative_interval 0
freq_recurrence_factor 4
active_start_date 20110316
active_end_date 99991231
active_start_time 154500
active_end_time 55959
date_created 2011-12-29 20:37:05.737
schedule_description Every 4 week(s) on Wednesday at 154500
next_run_date 20120104
next_run_time 154500

if the start date is March 16 2011 at 9:45, it doesn't make sense that the next run date would fall on January 4th 2012 based on the properties of the job.

I guess my question is, how much can I rely on this Next Run Date data?

Answer 1

Based on the Active Start Date Mar 16th 2011, in January 2012, we would expect a schedule occurrence to land on Wed Jan 18th.

If the modified date of that schedule becomes greater than the active start date, SQL than seems to use the modified date as the starting point from which to determine the next Wednesday to start the 4 week sequence.

If an update is happening on Tues Jan 10th, then the next run date becomes Wed Jan 11th and every subsequent Wednesday 4 weeks from there is now relative to Jan 11th which now omits Jan 18th from the original sequence.

The active start date seems to no longer have relevance to SQL server when calculating the next run date.

It will simply pick the next day in the future, starting from the modified date, that matches the requested weekday; in this case the next Wednesday.

Answer 2

When did you check this data? The msdb.dbo.sysjobschedules table refreshes every 20 minutes. So if you had it set and then changed it, then ran the sp_help_jobschedule stored procedure, the underlying data might not be updated yet.

What do you get for next_scheduled_run_date when you execute this query?

exec sp_help_jobactivity @job_name = 'YourJobScheduleName'