How SQL Server agent calculates next run date for a job

Question

I am developing a system for scheduling task & I am following Microsoft SQL agent approach to schedule task. I have replicated sysschedules table for capturing users scheduling occurrences and timings. Now I need to know how SQL Server get the Next_run_date and time from this table.

I have found solution on internet which are using sysjobhistory table to get the next run date, but in my case I have only sysschedules table. So, what is the formula to calculate the next_run_date and time for any job.

Note: I am not looking for entire code, concept/algorithm will work for me.

Answer 1

In the dbo.sysschedules table you will see all of the data needed to calculate the schedules.

The columns named active_* delimit the periods in which the jobs can run.

The columns named freq_* define the frequency of the job runs. You will notice a freq_type, which defines a type of frequency, which drives how the freq_interval and other columns are interpreted.

Please read the definitions at: http://msdn.microsoft.com/en-us/library/ms178644.aspx

EDIT: You can calculate the next_run_datetime by using one of the following:

sp_help_jobschedule @job_name = 'Jobname'
sp_help_jobschedule @job_id= '<job id guid>'

Since there can be several schedules you can also use the schedule_id as such:

sp_help_jobschedule @schedule_id = 1, @job_name = 'Jobname'

This will return a data set in which the last columns give the data, such as:

next_run_date   next_run_time   schedule_uid                             job_count
20141110        190000          301DC5D5-9569-49FA-8BC5-9129109AFA6A     1

And you can script out the msdb procedure sp_help_jobschedule to see the code in the procedure, in case you want to include some of that in your own script.