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.

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).
–
brianDec 30 '11 at 3:13

1

@brian Do you have any proof of that?? I'd be willing to dispute that comment.
–
Thomas StringerDec 30 '11 at 5:00

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?

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