Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 '11 at 3:13
1  
@brian Do you have any proof of that?? I'd be willing to dispute that comment. –  Thomas Stringer Dec 30 '11 at 5:00
    
yeah, quite not sure about brian's comment there –  towpse Dec 30 '11 at 17:20

3 Answers 3

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'
share|improve this answer
    
tried that query and i get 2012-01-04 15:45:00.000 for the next run date... –  towpse Dec 30 '11 at 14:13
    
@towpse What does your sp_help_jobschedule stored procedure return this morning now? Same value, or updated? –  Thomas Stringer Dec 30 '11 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? –  towpse Dec 30 '11 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. –  towpse Dec 30 '11 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. –  towpse Dec 30 '11 at 14:52
up vote 2 down vote accepted

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.

share|improve this answer

--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
LEFT OUTER JOIN
[msdb].[dbo].[sysjobactivity] AS act
ON act.job_id = job.job_id
GROUP BY job.name, job.description, job.enabled;

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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