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?

  • One thing to note, the scheduler likes things to be even. For example, if you schedule a job to run hourly, the job will get called every hour on the hour (even though you scheduled it at 12:15 and said it should start today).
    – brian
    Dec 30, 2011 at 3:13
  • 1
    @brian Do you have any proof of that?? I'd be willing to dispute that comment. Dec 30, 2011 at 5:00
  • yeah, quite not sure about brian's comment there
    – topwik
    Dec 30, 2011 at 17:20

5 Answers 5


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'
  • tried that query and i get 2012-01-04 15:45:00.000 for the next run date...
    – topwik
    Dec 30, 2011 at 14:13
  • @towpse What does your sp_help_jobschedule stored procedure return this morning now? Same value, or updated? Dec 30, 2011 at 14:19
  • same values. i even went into the mgmt studio UI to create a job manually from there not using my UI, inputting the same criteria and I get the same date as a result: 20120104 154500. maybe that date is correct?
    – topwik
    Dec 30, 2011 at 14:36
  • so my scheduler control that i'm using as the UI frontend for schedules/jobs stored against the sql DB's says that the next occurrence should happen on the 18th, but the next scheduled run date says the 4th.
    – topwik
    Dec 30, 2011 at 14:39
  • just double checked on a co workers machine by created a new test schedule/job with the same criteria and next scheduled run date is the 4th, which doesn't jive with what my scheduling calendar control is showing. hmmm.
    – topwik
    Dec 30, 2011 at 14:52

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.


This query can be useful, for history details you can join msdb.dbo.sysjobhistory

SELECT   job.name AS JobName,  
    job.description AS [Description],  
    job.enabled AS IsEnabled,  
    max(act.start_execution_date) AS StartExecutionDate,  
    max(act.next_scheduled_run_date) AS NextScheduledRunDate  
FROM     msdb.dbo.sysjobs AS job  
    [msdb].[dbo].[sysjobactivity] AS act  
    ON act.job_id = job.job_id  
GROUP BY job.name, job.description, job.enabled;

The Next Run field(s) on my Job Activity Monitor were not in agreement with that which was obviously configured in the Job Schedule..

In my case, it transpired that the SQL Server Agent service was not running (for some reason). I started the service and refreshed the Job Activity Monitor, and the data is now accurate.


msdb.dbo.sp_help_job always appears to return the correct actual next_run_date / next_run_time.

It uses sp_get_composite_job_info, which does the following call to actually retrieve the next_run_date/next_run_time:

      IF ((@@microsoftversion / 0x01000000) >= 8) -- SQL Server 8.0 or greater
        INSERT INTO @xp_results
        EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs, @job_owner, @job_id
        INSERT INTO @xp_results
        EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs, @job_owner

Since sysjobschedule appears to be unreliable just use sp_help_job.

If you need to manipulate the values returned you can use the openrowset trick:

      SELECT  * 
      FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
      , 'set fmtonly off exec msdb.dbo.sp_help_job')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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