When the scripts are run, the next_run_date is
recorded from sysjobschedules for each job and
schedule. To get the most up-to-date values for the
next_run_date, the scripts also call the (undocumented)
extended stored procedure master..xp_sqlagent_enum_
jobs, which queries the SQL Server Agent to return the
latest data. If the returned next-run values are later
than those in sysjobschedules, the script overwrites the
previously recorded values.
Once the scripts know that a job is scheduled to run
at a certain date, they can extrapolate all the run dates
for each schedule. Starting at the date in next_run_date,
the script adds the appropriate frequency interval (i.e.,
days, weeks, months) and records all run dates in the
#JobScheduledDates table, until the datetime value
in max_datetime_to_consider is reached. Next, the
script returns and determines all run dates between
next_run_date and min_datetime_to_consider.
After obtaining the run dates, you need to calculate
the times when jobs run in each run date. To
do so, you start with the active_start_time: If a job
is scheduled to run once a day, the active_start_time
is the runtime. If jobs run every several (say x)
minutes or hours, you add x minutes (or hours)
to the active_start_time until the end of the day
is reached.
Finding Missed Jobs and
Conflicting Schedules
The run dates and times are stored in the #JobScheduledDatesAndTimes
table. To find missed jobs, the
JobScript_FindMissedJobs.sql script compares the
content of this table with the values in sysjobhistory to
check whether the first step was invoked on (or a few
seconds after) the scheduled date and time. You can
now easily obtain conflicting schedules by comparing
the dates and times in #JobScheduledDatesAndTimes.
If two run dates and times are only several seconds
apart (the number of seconds is configurable and is
stored in the variable @TimeGapInSeconds), then the
schedules indeed conflict.
Special Situations
Obtaining the run dates can still be challenging for
some schedules, and the scripts address such cases.
One such example is when, every few months, a job
is scheduled to run on the third or last weekend day.
The code to determine whether a day is a weekend
or a business day depends on the @@DATEFIRST
settings; Listing 2 shows an example of this code. The
code in Web Listing 1 returns the nth day of the month
(where n is the freq_relative_interval value in Table 1
that corresponds to jobs with freq_type = 32 and the
freq_interval is either 9 or 10).
Quick Job Tracking
You can also query SQL Server Agent scheduling
information by using Windows Management Instrumentation
(WMI) or another scripting language
besides T-SQL. The main advantage of my scripts,
though, is that they can return missed jobs and
conflicting schedules quickly, using simple T-SQL
code, even when many SQL Server jobs are on the
server. However, the scripts have a couple potential
limitations. If you run the scripts against a large
time window (e.g., several years), they might take a
while to run. Additionally, the scripts don’t consider
scheduling changes. If a schedule changes at some
point in time, the information reported for the dates
prior to the change might be incorrect. If you keep in
mind these minor caveats, the scripts should help you
improve your ability to keep a closer watch on SQL
Server job scheduling.