Job Execution Dates-Times Calculation (Past & Fut)

I have a tricky question to Microsoft SQL Server 2000/2005. I have failed to find a solution to the problem yet and others have failed too. So I thought that I throw it around here, because the fact that a lot of knowledgeable DBAs hang around in this forum.

I am looking for a script/stored procedure that is able to show me upcoming job executions for a selected date/time range based on the current settings for the jobs configured on the Database server.

Example
Date/Time From: 2/16/2007 00:00 a.m. (Friday)
Date/Time To: 2/17/2007 12:00 p.m. (Saturday)

A. Job 1 / Schedule 1 = Week Days, 1:00 a.m.
B. Job 1 / Schedule 2 = Saturdays, 10:00 a.m.
C. Job 2 / Schedule 1 = hourly, between 10:00 a.m. and 1:00 p.m.
D. Job 3 / Schedule 1 = every 3rd Saturday of the month, 5:00 a.m.
E. Job 4 / Schedule 1 = every 1st Friday of the month, 8:00 a.m.

The routine I have in mind would not return Job E, because 2/16/2007 is the 3rd Friday of the month and not the first.

Job A: 2/16/2007 01:00 a.m.
Job B: 2/16/2007 10:00 a.m.
Job B: 2/16/2007 11:00 a.m.
Job B: 2/16/2007 12:00 p.m.
Job B: 2/16/2007 01:00 p.m.
Job D: 2/17/2007 05:00 a.m.
Job A: 2/17/2007 10:00 a.m.
Job B: 2/17/2007 10:00 a.m.
Job B: 2/17/2007 11:00 a.m.
Job B: 2/17/2007 12:00 p.m.

Output

It needs to return the Job ID, the Job Name, the Schedule ID, the Date and the Time.
Disabled Jobs and Schedules are by default excluded from the selection, but an option to include or exclude those would be a bonus.
Information such as the min, max and average execution time would be great too.

Notes

The schedules of Job D and Job B overlap as you can see in my example above.
This happens only once per month though. I have over 20 jobs with sometimes very frequent execution times, like every 5 minutes or every 20 minutes and jobs that run hourly, daily, weekdays only, weekends only, monthly once etc.

Purpose

I want to do two things.
I want to determine where jobs overlap, not just by start date/time, but also by average run time and maximum run time.

I also want to be able to generate a report that shows me what should have been done and what was actually done by the jobs (note on the site, 6 of the jobs create new jobs on the fly for other database servers and this is sometimes not happening properly, without getting any error message. The volume of jobs makes the manual search like a search for a needle in a haystack.)

Findings so far:

I did some digging myself and found following stored procedures that do some of the steps that I need and involved tables for the calculation.

Stored procedures:
- sp_get_schedule_description (in db: msdb) (undocumented stored procedure)
- sp_add_schedule (in db: msdb) http://msdn2.microsoft.com/en-us/library/ms187320.aspx

Tables:
- msdb.sysjobs
- msdb.sysjobschedules
- msdb.sysjobhistory

It is not a problem to determine the next execution of a job, but that is not what I need, anyway.
The problem is that it does not help you to determine all upcoming execution times, if the selected timeframe is long enough that SQL Server executes the job more than once.

There is no way around using the sysjobschedules table and calculate the execution dates and times based on the configured settings. See the Stored procedure: sp_get_schedule_description.

That one breaks down nicely the settings as documented for sp_add_schedule at
http://msdn2.microsoft.com/en-us/library/ms187320.aspx, but it does not allow the determination of the exact upcoming dates and times when the job is supposed to be executed.

Another Example
If there is only one job scheduled to run

1) every 5 minutes,
2) on every weekday
3) between 1:30pm and 2:00pm

You would get the following results

1) start date/time 7/7/2007 12:00pm, end date/time 7/8/2007 2:00pm

nothing, because the 7/7/2007 and 7/8/2007 are on the weekend

2) start date/time 7/5/2007 12:00pm, end date/time 7/5/2007 1:45pm

7/5/2007 1:30pm
7/5/2007 1:35pm
7/5/2007 1:40pm
7/5/2007 1:45pm

3) start date/time 7/5/2007 1:45pm, end date/time 7/6/2007 3:00pm

7/5/2007 1:45pm
7/5/2007 1:50pm
7/5/2007 1:55pm
7/5/2007 2:00pm
7/6/2007 1:30pm
7/6/2007 1:35pm
7/6/2007 1:40pm
7/6/2007 1:45pm
7/6/2007 1:50pm
7/6/2007 1:55pm
7/6/2007 2:00pm

Now SQL has a lot more configuration options for the scheduler.

And don't forget that you can have more than one schedule record for any single job, including no-schedule record (which would not interest me).

Autom. when SQL starts freq_type=64
Starts when CPU idle freq_type=128

One Time On Date mm/dd/yyyy at time: hh:mm:ss am/pmfreq_type=1
or
Recurring

Occurs
Daily freq_type=4
Every x day(s)freq_interval=x
Weekly freq_type=8
Every x week(s) onfreq_recurrence_factor=x

Mo [ ], Tu [ ], We [ ], Th [ ],
Fr [ ], Sa [ ], Su [ ],

1 = Sunday, 2 = Monday, 4 = Tuesday, 8 = Wednesday, 16 = Thursday, 32 = Friday, 64 = Saturday. Examples: Su and Mo enabled = 3 (1 (Su) + 2 (Mo)), Mo, We and Fr enabled = 42 (2 (Mo) + 8 (We) + 32 (Fr))
Monthly freq_type=16
Day X of every Y month(s)freq_interval=X
freq_recurrence_factor=Y
or

The 1st,2nd,3rd,4th,LAST WEEKDAYfreq_type=32
of every Y month(s)

freq_relative_interval=1,2,4 (3rd),8 (4th),16(last)
freq_interval= 1=Su,2=Mo,3=Tu,4=We,5=Th,6=Fr,7=Sa,8=Day,9=Weekday,10=Weekend day
freq_recurrence_factor=Y

Occurs Once at hh:mm:ss AM/PMfreq_subday_type=0x1
or
occurs Every X Hours/Minutes
Starting: hh:mm:ss A/PM freq_subday_type=0x4 (minutes) or 0x8 (hours)
Ending: hh:mm:ss A/PMfreq_subday_interval=X
active_start_time
active_end_time

Start Date mm/dd/yyy End Date mm/dd/yyyyactive_start_date
oractive_end_date
No End Dateactive_end_date=99991231

Does anybody has a script that does that or several individual scripts that would have to be combined to do what I want to do?

Thanks. I appreciate it.

Cheers!

Carsten Cumbrowski
http://www.sqlhunt.com/