/* =================================================================
The following script is useful to find missed SQL jobs:
Jobs and schedules that were supposed to run but were skipped
and did not start on time, for any reason (e.g., because the SQL
Server Agent was not running, due to long-running jobs, and so on).
The script is version-aware and works on SQL Server 2000 and 2005.
Note: The script only examines enabled jobs and enabled schedules.
The actions taken by the script are as follows:
The script accepts a start-time (@StartDateTimeToCalculate)
and an end-time (@EndDateTimeToCalculate), which should be specified
by the user. By default, the script analyzes all jobs on the server.
If you wish to only analyze a single job, then specify the job_id
for the desired job in the variable @JobID.
Step 1 - The script captures a snapshot of all needed SQL job
information, including the job schedules, first step to run,
active start & end time, creation date, and so on - everything
that is needed to determine the dates and times when the jobs
should have been run.
Step 2 - Reconstruct and revese engineer all the dates in which
all jobs (or only the job in @JobID) are scheduled to run, between
the dates @StartDateTimeToCalculate and @EndDateTimeToCalculate
(note - if the script finds that these dates are two far apart,
it will normalize the time window and only consider relevant times).
This is done in several steps:
Step 2.1 - jobs that are scheduled to run once
Step 2.2 - daily jobs
Step 2.3 - monthly (fixed) jobs
Step 2.4 - monthly (relative) jobs.
For more information about fixed and relative monthly
jobs, please see the SQL Server Books Online
(look up sysjobschedules in SQL 2000 or sysschedules in 2005).
The reconstructed run-dates found in Step 2 are stored in a temp
table called #JobScheduledDates.
Step 3 - Find the run-times (times in which jobs are scheduled
to run) for all the run-dates found in Step 2. The dates and times
in which the SQL jobs are scheduled to run are stored in the temp
table called #JobScheduledDatesAndTimes.
Step 4 - Find all missed jobs by comparing the data in the table
#JobScheduledDatesAndTimes to the data found in sysjobhistory.
If any jobs did not fire after X amount of seconds after their
scheduled date and time (and X is configurable as the variable
@TimeSlackGapInSeconds) then all such encounters are considered
as missed jobs and are reported back to the user.
User parameters:
@StartDateTimeToCalculate - start time to examine
@EndDateTimeToCalculate - end time to examine
@JobID - if users wish to analyze one job, then
set this variable to the wanted job_id value.
To get info for all server jobs - leave @JobID NULL.
@TimeSlackGapInSeconds - see explanation for Step 4 above.
Note: The script is optimized for readability. Not for speed.
In spite of this fact, the script still runs fast. The longer
the time window is- the more time it will take for the script
to run.
=================================================================== */
DECLARE @StartDateTimeToCalculate DATETIME
DECLARE @EndDateTimeToCalculate DATETIME
DECLARE @TimeSlackGapInSeconds INT
DECLARE @JobID NVARCHAR(64)
-- Time window to find missed jobs
SET @StartDateTimeToCalculate = DATEADD(HOUR, -10, GETDATE())
SET @EndDateTimeToCalculate = GETDATE()
-- If a job started a few seconds after its schedule
-- then it will not be considered as a missed run.
-- The number of seconds to allow is @TimeSlackGapInSeconds.
SET @TimeSlackGapInSeconds = 5
-- To look at a specific job, specify the job id below.
-- To look at all jobs- leave it NULL.
-- Validate start and end times
IF @StartDateTimeToCalculate > @EndDateTimeToCalculate
BEGIN
RAISERROR('The start and end dates entered are not valid. Aborting.', 16, 1)
RETURN
END
IF @EndDateTimeToCalculate <= GETDATE()
BEGIN
SET @EndDateTimeToCalculate = GETDATE()
END
-- Validate that the request job exists (if @JobID or @JobName are non-null)
IF @JobID IS NOT NULL
AND NOT EXISTS ( SELECT *
FROM msdb.dbo.sysjobs
WHERE job_id = @JobID )
BEGIN
RAISERROR('The requested job id does not exist on the server.', 16, 1)
RETURN
END
SET NOCOUNT ON
/* ========================================================
Step 1 - Take a snapshot of all jobs and schedules.
We start by recording the info for all jobs and schedules.
This info is first taken from the msdb database. Some fields
in msdb may not be accurate, so we also override the msdb
info (if needed) with information from the SQL Agent itself.
There are 2 goals here:
1. Take a snapshot of all jobs so if any new jobs and schedules
are changed on the server when the script runs, they will
not be considered.
2. Calculate a next_run_date and next_run_time for each job.
We only need this info to identify a date & time value
where the job was run or was scheduled to run. This
will help us to reverse-engineer the schedule of the job
between @StartDateTimeToCalculate and @EndDateTimeToCalculate.
This info is not used for any other purposes.
====================================================== */
-- Get a snapshot of all enabled jobs and their associated schedule-parameters.
-- Note: The next run date stored in SQL Server (sysjobschedules) may not be accurate.
-- We begin by taking the values of next_run_date and next_run_time from sysjobschedules
-- and then override this value with the information return from the SQL Server Agent, if available.
IF OBJECT_ID('tempdb..#JobScheduleInfo') IS NOT NULL
DROP TABLE #JobScheduleInfo
CREATE TABLE #JobScheduleInfo (job_id VARCHAR(64),
job_name NVARCHAR(128) COLLATE DATABASE_DEFAULT,
job_owner NVARCHAR(128) COLLATE DATABASE_DEFAULT,
schedule_id INT,
schedule_name NVARCHAR(128) COLLATE DATABASE_DEFAULT,
freq_type INT,
freq_interval INT,
freq_subday_type INT,
freq_subday_interval INT,
freq_relative_interval INT,
freq_recurrence_factor INT,
active_start_date INT,
active_end_date INT,
active_start_time INT,
active_end_time INT,
date_created DATETIME,
min_datetime_to_consider DATETIME, -- Indicates the min datetime to consider when returning results.
max_datetime_to_consider DATETIME, -- Indicates the max datetime to consider when returning results.
start_step_id INT,
next_run_date INT, -- This value may be 0 or NULL in sysjobschedules, or may not be up to date.
next_run_time INT)
-- This value may be 0 or NULL in sysjobschedules, or may not be up to date.
INSERT INTO #JobScheduleInfo WITH (TABLOCK)
SELECT CAST(a.job_id AS VARCHAR(64)),
a.name,
SUSER_SNAME(a.owner_sid),
b.schedule_id,
b.name,
b.freq_type,
b.freq_interval,
b.freq_subday_type,
b.freq_subday_interval,
b.freq_relative_interval,
b.freq_recurrence_factor,
b.active_start_date,
b.active_end_date,
b.active_start_time,
b.active_end_time,
a.date_created,
-- Initial value (updated later)
DATEADD(ss,
CAST(active_start_time / 10000 AS INT) * 3600 + (CAST(active_start_time / 100 AS INT) % 100)
* 60 + (active_start_time % 100), CAST(CAST(active_start_date AS VARCHAR(8)) AS DATETIME)),
-- Initial value (updated later)
DATEADD(ss,
CAST(active_end_time / 10000 AS INT) * 3600 + (CAST(active_end_time / 100 AS INT) % 100) * 60
+ (active_end_time % 100), CAST(CAST(active_end_date AS VARCHAR(8)) AS DATETIME)),
a.start_step_id,
z.next_run_date, -- This value may be 0 or NULL in sysjobschedules, or may not be up to date.
z.next_run_time -- This value may be 0 or NULL in sysjobschedules, or may not be up to date.
FROM msdb.dbo.sysjobs a
INNER JOIN msdb.dbo.sysjobschedules z ON a.job_id = z.job_id
INNER JOIN msdb.dbo.sysschedules b ON z.schedule_id = b.schedule_id
AND a.enabled = 1
AND b.enabled = 1
WHERE (@JobID IS NULL
OR a.job_id = @JobID)
OPTION (RECOMPILE)
CREATE UNIQUE CLUSTERED INDEX JobScheduleInfo ON #JobScheduleInfo (job_id, schedule_name)
-- Update min_datetime_to_consider - update occurs if date_created is greater than the active_start_date + time
-- or when @StartDateTimeToCalculate is greater than both of these values.
UPDATE #JobScheduleInfo
SET min_datetime_to_consider = CASE WHEN date_created < @StartDateTimeToCalculate THEN @StartDateTimeToCalculate
ELSE date_created
END
WHERE -- Only update if the min_datetime_to_consider is smaller than the other dates.
min_datetime_to_consider < CASE WHEN date_created < @StartDateTimeToCalculate THEN @StartDateTimeToCalculate
ELSE date_created
END
-- Similarly, update the max_date_to_consider
UPDATE #JobScheduleInfo
SET max_datetime_to_consider = @EndDateTimeToCalculate
WHERE max_datetime_to_consider > @EndDateTimeToCalculate
-- Only update if the max_datetime_to_consider is greater than the other date.
-- Check for bogus values- when max_datetime_to_consider <= min_datetime_to_consider
UPDATE #JobScheduleInfo
SET max_datetime_to_consider = min_datetime_to_consider
WHERE max_datetime_to_consider <= min_datetime_to_consider
-- In order to get an accurate next_run_date or next_run_time,
-- we refer to the SQL agent and get whatever information is available there.
-- This is done by calling the (undocumented) extended stored proc
-- xp_sqlagent_enum_jobs. Note that xp_sqlagent_enum_jobs will only give
-- us the next run date & time for one schedule for the job.
-- In spite of this fact, we still call it because it may contain better
-- info than sysjobschedules, even if it only for a few jobs and schedules.
IF OBJECT_ID('tempdb..#xp_results') IS NOT NULL
DROP TABLE #xp_results
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id SYSNAME COLLATE database_default
NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
-- Prerequisites: In SQL 2K5 and above, the 'Agent XPs' server configuration
-- option must be set to true. The script assumes that it is true.
-- See sp_configure for additional information.
DECLARE @CurrJobOwner NVARCHAR(128)
SELECT @CurrJobOwner = MIN(job_owner)
FROM #JobScheduleInfo
WHILE @CurrJobOwner IS NOT NULL
BEGIN
INSERT INTO #xp_results
EXEC master.dbo.xp_sqlagent_enum_jobs
1,
@CurrJobOwner
SELECT @CurrJobOwner = MIN(job_owner)
FROM #JobScheduleInfo
WHERE job_owner > @CurrJobOwner
END
-- Update #JobScheduleInfo with the information from #xp_results
-- for the relevant jobs and schedules. We do not update records
-- where the next_run_date is null or zero in #xp_results.
-- Those entries mean that the SQL Agent has not yet calculated
-- the next run date & time.
UPDATE a
SET next_run_date = b.next_run_date,
next_run_time = b.next_run_time
FROM #JobScheduleInfo a
INNER JOIN #xp_results b ON a.job_id = b.job_id
AND a.schedule_id = b.next_run_schedule_id
AND b.next_run_date IS NOT NULL
AND b.next_run_time IS NOT NULL
AND b.next_run_date > 0
/* ========================================================================
Step 2 - Find all dates between @StartDateTimeToCalculate
and @EndDateTimeToCalculate in which the job is
scheduled to run.
For each job and every schedule in #JobScheduleInfo, we now get the dates
and the first time in each date in which the job is scheduled to run.
We create the temp table #JobScheduledDates for this purpose, which includes
the following fields:
RunDate - dates to run (datetime)
StartTimeEx (normalized format for active_start_time)
StartTimeInSecFromStartOfDay (# of seconds from the
beginning of the day when the job runs for the first time
in that day)
=========================================================================== */
IF OBJECT_ID('tempdb..#JobScheduledDates') IS NOT NULL
DROP TABLE #JobScheduledDates
CREATE TABLE #JobScheduledDates (job_id VARCHAR(64),
schedule_name NVARCHAR(128),
RunDate DATETIME,
StartTimeEx CHAR(6),
StartTimeInSecFromStartOfDay INT)
/* =========================================================
Step 2.1 - jobs that run once - freq_type = 1
freq_type = 1 for jobs that are scheduled to run once,
so calculating the RunDate and StartTime is not difficult
and can be done for all server jobs in one statement.
-- ======================================================= */
INSERT INTO #JobScheduledDates WITH (TABLOCK)
(job_id,
schedule_name,
RunDate,
StartTimeEx)
SELECT job_id,
schedule_name,
CAST(CAST(active_start_date AS VARCHAR(8)) AS DATETIME),
-- The format for active_start_time is hhnnss
-- and h, n, and s can be missing (if zero).
-- In other words, we need to normalize all start times to 6 chars
-- before we can start processing them efficiently.
REPLICATE('0', 6 - LEN(LTRIM(RTRIM(CAST(active_start_time AS VARCHAR(6))))))
+ LTRIM(RTRIM(CAST(active_start_time AS VARCHAR(6))))
FROM #JobScheduleInfo
WHERE freq_type = 1
AND CAST(CAST(active_start_date AS VARCHAR(8)) AS DATETIME) BETWEEN @StartDateTimeToCalculate
AND @EndDateTimeToCalculate
CREATE UNIQUE CLUSTERED INDEX JobScheduledDates ON #JobScheduledDates (job_id, schedule_name, RunDate)
/* ==========================================================================
Step 2.2 - daily jobs - freq_type = 4 (freq_subday_type = 1, 4, 8)
This case is when a job runs every X days (freq_interval = X).
Calculating the dates for these jobs is not straightforward.
In order to figure out when the job was scheduled to run in the past
or will run in the future, we need to look at the next_run_date.
The reason is- if I schedule a job to run (say) every 5 days,
the first date in which the job will run is non-deterministic
and depends on the job creation date, the computer clock, and other
parameters. In other words, we look at next_run_date to latch
onto a deterministic date that is associated with the job.
This is done in a loop for each job and schedule.
Note: We only need to calculate scheduled run dates that fall between
MAX(active_start_date, @StartDateTimeToCalculate)
and MIN(active_end_date + 1, @EndDateTimeToCalculate).
========================================================================== */
DECLARE @CurrJobID UNIQUEIDENTIFIER
DECLARE @CurrScheduleName NVARCHAR(128)
DECLARE @CurrNextRunDate DATETIME
DECLARE @CurrActiveStartTime INT
DECLARE @CurrRunsEveryXDays INT
-- this variable holds X.
DECLARE @i INT
DECLARE @CurrMinDate DATETIME
DECLARE @CurrMaxDate DATETIME
-- @MinDate and @MaxDate reflect the time window in which
-- we need to calculate the run dates for each job.
-- These dates depend on active_start_date therefore may vary for each job.
-- We loop for each job_id and each schedule_name.
SELECT @CurrJobID = MIN(job_id)
FROM #JobScheduleInfo
WHERE freq_type = 4
-- If next_run_date is 0 or NULL for all the schedules for the job,
-- then there are no deterministic parameters and we cannot guarantee
-- the accuracy of the info so we do not address this case.
AND next_run_date IS NOT NULL
AND next_run_date > 0
WHILE @CurrJobID IS NOT NULL
BEGIN
SELECT @CurrScheduleName = MIN(schedule_name)
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND freq_type = 4
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL
AND next_run_date > 0
WHILE @CurrScheduleName IS NOT NULL
BEGIN
SELECT @CurrActiveStartTime = active_start_time,
@CurrRunsEveryXDays = freq_interval,
@CurrNextRunDate = CAST(CAST(next_run_date AS NVARCHAR(8)) AS DATETIME),
@CurrMinDate = min_datetime_to_consider,
@CurrMaxDate = max_datetime_to_consider
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName
-- Record all dates between @CurrMinDate and @CurrMaxDate
-- in which the job is scheduled to run. This is done as follows:
-- Starting with the next_run_date, we add/subtract @CurrRunsEveryXDays until
-- we fill all dates between @CurrMinDate and @CurrMaxDate.
SET @i = 0
-- First, get all dates between next_run_date up to @CurrMaxDate
WHILE DATEADD(dd, @i * @CurrRunsEveryXDays, @CurrNextRunDate) <= @CurrMaxDate
BEGIN
-- Check that we need to record this date
IF DATEADD(dd, @i * @CurrRunsEveryXDays, @CurrNextRunDate) >= @CurrMinDate
BEGIN
-- Check that it has not yet been recorded.
IF NOT EXISTS ( SELECT *
FROM #JobScheduledDates
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName
AND RunDate = DATEADD(dd, @i * @CurrRunsEveryXDays,
@CurrNextRunDate) )
BEGIN
INSERT INTO #JobScheduledDates
(job_id,
schedule_name,
RunDate,
StartTimeEx)
SELECT @CurrJobID,
@CurrScheduleName,
DATEADD(dd, @i * @CurrRunsEveryXDays, @CurrNextRunDate),
-- The format for active_start_time is hhnnss
-- and h, n, and s can be missing (if zero).
-- In other words, we need to normalize all start times to 6 chars
-- before we can start processing them efficiently.
REPLICATE('0',
6
- LEN(LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))))
+ LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))
WHERE NOT EXISTS ( SELECT TOP 1
*
FROM #JobScheduledDates
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName
AND RunDate = DATEADD(dd,
@i
* @CurrRunsEveryXDays,
@CurrNextRunDate) )
END
END
SET @i = @i + 1
END
SET @i = 0
-- Now, get all the dates between @CurrMinDate and next_run_date
WHILE DATEADD(dd, -@i * @CurrRunsEveryXDays, @CurrNextRunDate) >= @CurrMinDate
BEGIN
-- Check that we need to record this date
IF DATEADD(dd, -@i * @CurrRunsEveryXDays, @CurrNextRunDate) <= @CurrMaxDate
BEGIN
-- Check that it has not yet been recorded.
IF NOT EXISTS ( SELECT *
FROM #JobScheduledDates
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName
AND RunDate = DATEADD(dd, @i * @CurrRunsEveryXDays,
@CurrNextRunDate) )
BEGIN
INSERT INTO #JobScheduledDates
(job_id,
schedule_name,
RunDate,
StartTimeEx)
SELECT @CurrJobID,
@CurrScheduleName,
DATEADD(dd, -@i * @CurrRunsEveryXDays, @CurrNextRunDate),
-- The format for active_start_time is hhnnss
-- and h, n, and s can be missing (if zero).
-- In other words, we need to normalize all start times to 6 chars
-- before we can start processing them efficiently.
REPLICATE('0',
6
- LEN(LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))))
+ LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))
WHERE NOT EXISTS ( SELECT TOP 1
*
FROM #JobScheduledDates
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName
AND RunDate = DATEADD(dd,
-@i
* @CurrRunsEveryXDays,
@CurrNextRunDate) )
END
END
SET @i = @i + 1
END
-- Move on to the next daily schedule for the same job
SELECT @CurrScheduleName = MIN(schedule_name)
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND freq_type = 4
AND schedule_name > @CurrScheduleName
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL
AND next_run_date > 0
END
SELECT @CurrJobID = MIN(job_id)
FROM #JobScheduleInfo
WHERE freq_type = 4
AND job_id > @CurrJobID
-- If next_run_date is 0 or NULL for all the schedules for the job,
-- then there are no deterministic parameters and we cannot guarantee
-- the accuracy of the info so we do not address this case.
AND next_run_date IS NOT NULL
AND next_run_date > 0
END
/* ==========================================================================
Step 2.2 - weekly jobs - freq_type = 8 (freq_subday_type = 1, 4, 8)
This case is when a job runs every X weeks in specific days in each week.
Calculating the dates for these jobs is not straightforward: Say that a
job runs every 3 weeks on Sunday and Friday, and that the job was created
on Thursday. Will the next run be on the next Sunday, or since the job
runs on the following Friday- will it not run on the next Sunday?
The following logic is used to get the schedule for weekly jobs:
Check the next run date for the job. That week is when the job is run.
Then, look at every day of that week (we can ignore DATEFIRST since
it doesn't apply here) and conclude all days in that week in which
the job should run (this info is captured by freq_interval).
This allows us to latch on to deterministic dates for the job schedule.
Then, we calculate dates before and after that date in which the job should run.
========================================================================== */
DECLARE @CurrRunEveryXWeeks INT
-- This variable captures X.
DECLARE @CurrDayVector INT
-- This variable captures the day codes when the job runs
-- (1-Sun, 2-Mon, ..., 7-Sat), as taken from freq_interval.
DECLARE @DayCodeForNextRunDate INT
DECLARE @tmpMinDate DATETIME
DECLARE @tmpMaxDate DATETIME
-- @MinDate and @MaxDate reflect the time window in which
-- we need to calculate the run dates for each job.
-- These dates depend on active_start_date therefore may vary for each job.
-- First, we create a temp table that holds the week days.
-- This will help us to find the scheduled dates for weekly jobs later.
IF OBJECT_ID('tempdb..#DaysAndDates') IS NOT NULL
DROP TABLE #DaysAndDates
CREATE TABLE #DaysAndDates (DayCode INT PRIMARY KEY CLUSTERED,
DayDesc VARCHAR(16), -- Has no functional use. For readability only.
TempFlag BIT, -- If this value is 1 for a given job, then the job runs in scheduled to run that day.
TempDate DATETIME)
-- If this value is non-null for a given job, then the job runs in this scheduled date.
SET @i = 1
WHILE @i <= 7
BEGIN
INSERT INTO #DaysAndDates
(DayCode,
DayDesc,
TempFlag)
VALUES (@i,
CASE WHEN @i = 1 THEN 'Sunday'
WHEN @i = 2 THEN 'Monday'
WHEN @i = 3 THEN 'Tuesday'
WHEN @i = 4 THEN 'Wednesday'
WHEN @i = 5 THEN 'Thursday'
WHEN @i = 6 THEN 'Friday'
WHEN @i = 7 THEN 'Saturday'
END,
0)
SET @i = @i + 1
END
-- We loop for each job_id and each schedule_name.
SELECT @CurrJobID = MIN(job_id)
FROM #JobScheduleInfo
WHERE freq_type = 8
-- If next_run_date is 0 or NULL for all the schedules for the job,
-- then there are no deterministic parameters and we cannot guarantee
-- the accuracy of the info so we do not address this case.
AND next_run_date IS NOT NULL
AND next_run_date > 0
WHILE @CurrJobID IS NOT NULL
BEGIN
SELECT @CurrScheduleName = MIN(schedule_name)
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND freq_type = 8
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL
AND next_run_date > 0
WHILE @CurrScheduleName IS NOT NULL
BEGIN
SELECT @CurrActiveStartTime = active_start_time,
@CurrDayVector = freq_interval,
@CurrNextRunDate = CAST(CAST(next_run_date AS NVARCHAR(8)) AS DATETIME),
@CurrRunEveryXWeeks = freq_recurrence_factor,
@CurrMinDate = min_datetime_to_consider,
@CurrMaxDate = max_datetime_to_consider
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName
-- Record all dates between @CurrMinDate and @CurrMaxDate
-- in which the job is scheduled to run. This is done as follows:
-- Starting with the next_run_date, we add/subtract @CurrRunsEveryXWeeks
-- for each day in which the job is scheduled to run, until
-- we fill all dates between @CurrMinDate and @CurrMaxDate.
-- Get the days in which the job runs - Reinitialize #DaysAndDates
-- and then set TempFlag = 1 in #DaysAndDates for each run day.
UPDATE #DaysAndDates
SET TempFlag = 0,
TempDate = NULL
-- The POWER function here translates between the day codes (1-7)
-- to the day vector (1, 2, 4, ..., 64) in freq_interval.
UPDATE #DaysAndDates
SET TempFlag = 1
WHERE @CurrDayVector & POWER(2, DayCode - 1) > 0
-- Find the code of the day that corresponds to next_run_date
SET @DayCodeForNextRunDate = 1 + (DATEDIFF(dd, CONVERT(DATETIME, '1899/12/31', 120), @CurrNextRunDate)
% 7)
-- Mark next_run_date as the TempDate for the appropriate day in #DaysAndDates.
-- Here we also update the TempDates for all days in which the job runs.
UPDATE #DaysAndDates
SET TempDate = DATEADD(dd, DayCode - @DayCodeForNextRunDate, @CurrNextRunDate)
WHERE TempFlag = 1
SELECT @tmpMinDate = MIN(TempDate),
@tmpMaxDate = MAX(TempDate)
FROM #DaysAndDates
WHERE TempFlag = 1
-- At this point we are ready to loop and see which dates fall between @CurrMinTime and @CurrMaxTime
SET @i = 0
-- First, get all dates between the initial dates in #DaysAndDates up to @CurrMaxDate
WHILE DATEADD(dd, 7 * @i * @CurrRunEveryXWeeks, @tmpMinDate) <= @CurrMaxDate
BEGIN
-- Check that the max date in #JobScheduledDates is greater that the @CurrMinDate.
-- If it is not- none of the values in that table are in the time window
-- so there's nothing to write.
IF DATEADD(dd, 7 * @i * @CurrRunEveryXWeeks, @tmpMaxDate) >= @CurrMinDate
BEGIN
INSERT INTO #JobScheduledDates
(job_id,
schedule_name,
RunDate,
StartTimeEx)
SELECT @CurrJobID,
@CurrScheduleName,
DATEADD(dd, 7 * @i * @CurrRunEveryXWeeks, a.TempDate),
-- The format for active_start_time is hhnnss
-- and h, n, and s can be missing (if zero).
-- In other words, we need to normalize all start times to 6 chars
-- before we can start processing them efficiently.
REPLICATE('0',
6 - LEN(LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))))
+ LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))
FROM #DaysAndDates a
LEFT OUTER JOIN #JobScheduledDates b ON b.job_id = @CurrJobID
AND b.schedule_name = @CurrScheduleName
AND b.RunDate = DATEADD(dd,
7 * @i
* @CurrRunEveryXWeeks,
a.TempDate)
AND a.TempFlag = 1
WHERE b.job_id IS NULL
AND DATEADD(dd, 7 * @i * @CurrRunEveryXWeeks, a.TempDate) <= @CurrMaxDate
AND DATEADD(dd, 7 * @i * @CurrRunEveryXWeeks, a.TempDate) >= @CurrMinDate
END
SET @i = @i + 1
END
-- Now, look for all dates between @CurrMinDate and the initial dates in #DaysAndDates.
-- We start with the dates in #DaysAndDates an subtract X weeks at a time.
SET @i = 0
WHILE DATEADD(dd, -7 * @i * @CurrRunEveryXWeeks, @tmpMaxDate) >= @CurrMinDate
BEGIN
-- Check that the min date in #JobScheduledDates is greater that the @CurrMaxDate.
-- If it is not- none of the values in that table are in the time window
-- so there's nothing to write.
IF DATEADD(dd, -7 * @i * @CurrRunEveryXWeeks, @tmpMinDate) <= @CurrMaxDate
BEGIN
INSERT INTO #JobScheduledDates
(job_id,
schedule_name,
RunDate,
StartTimeEx)
SELECT @CurrJobID,
@CurrScheduleName,
DATEADD(dd, -7 * @i * @CurrRunEveryXWeeks, a.TempDate),
-- The format for active_start_time is hhnnss
-- and h, n, and s can be missing (if zero).
-- In other words, we need to normalize all start times to 6 chars
-- before we can start processing them efficiently.
REPLICATE('0',
6 - LEN(LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))))
+ LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))
FROM #DaysAndDates a
LEFT OUTER JOIN #JobScheduledDates b ON b.job_id = @CurrJobID
AND b.schedule_name = @CurrScheduleName
AND b.RunDate = DATEADD(dd,
-7 * @i
* @CurrRunEveryXWeeks,
a.TempDate)
AND a.TempFlag = 1
WHERE b.job_id IS NULL
AND DATEADD(dd, -7 * @i * @CurrRunEveryXWeeks, a.TempDate) <= @CurrMaxDate
AND DATEADD(dd, -7 * @i * @CurrRunEveryXWeeks, a.TempDate) >= @CurrMinDate
END
SET @i = @i + 1
END
-- Move on to the next weekly schedule for the same job
SELECT @CurrScheduleName = MIN(schedule_name)
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND schedule_name > @CurrScheduleName
AND freq_type = 8
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL
AND next_run_date > 0
END
-- Get the next job with a weekly schedule
SELECT @CurrJobID = MIN(job_id)
FROM #JobScheduleInfo
WHERE freq_type = 8
AND job_id > @CurrJobID
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL
AND next_run_date > 0
END
/* ==========================================================================
Step 2.3 - monthly (fixed) jobs - freq_type = 16 (freq_subday_type = 1, 4, 8)
This case is when a job runs every X months, at the Rth day of the month.
The Rth day can be the 29th, 30th, or 31st of the month, which may not exist
for some months. If the Rth day of the month indeed does not exist, then
that month is skipped.
The following logic is used to get the schedule for monthly (fixed) jobs:
Check the next run date for the job. That month is one when the job is run.
Then, add or subtract X month to find all dates in the requested time window
where the job should be run. For each month found - check whether the Rth date
of the month exist. If not- skip another X months to the next schedule.
(Checking whether the Rth day of the month exists is only done when R > 28).
========================================================================== */
DECLARE @CurrRunEveryXMonths INT
-- This variable captures X.
DECLARE @CurrDayOfMonthToRun INT
-- This variable captures the day codes when the job runs
-- (1-Sun, 2-Mon, ..., 7-Sat), as taken from freq_interval.
-- @MinDate and @MaxDate reflect the time window in which
-- we need to calculate the run dates for each job.
-- These dates depend on active_start_date therefore may vary for each job.
-- We loop for each job_id and each schedule_name.
SELECT @CurrJobID = MIN(job_id)
FROM #JobScheduleInfo
WHERE freq_type = 16
-- If next_run_date is 0 or NULL for all the schedules for the job,
-- then there are no deterministic parameters and we cannot guarantee
-- the accuracy of the info so we do not address this case.
AND next_run_date IS NOT NULL
AND next_run_date > 0
WHILE @CurrJobID IS NOT NULL
BEGIN
SELECT @CurrScheduleName = MIN(schedule_name)
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND freq_type = 16
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL
AND next_run_date > 0
WHILE @CurrScheduleName IS NOT NULL
BEGIN
SELECT @CurrActiveStartTime = active_start_time,
@CurrDayOfMonthToRun = freq_interval,
@CurrNextRunDate = CAST(CAST(next_run_date AS NVARCHAR(8)) AS DATETIME),
@CurrRunEveryXMonths = freq_recurrence_factor,
@CurrMinDate = min_datetime_to_consider,
@CurrMaxDate = max_datetime_to_consider
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName
-- Record all dates between @CurrMinDate and @CurrMaxDate
-- in which the job is scheduled to run. This is done as follows:
-- Starting with the next_run_date, we add/subtract @CurrRunsEveryXMonths
-- for each day in which the job is scheduled to run, until
-- we fill all dates between @CurrMinDate and @CurrMaxDate.
-- At this point we are ready to loop and see which dates fall between @CurrMinTime and @CurrMaxTime.
-- While looping, we need to make sure that the day at which the job runs
-- indeed exists for the month (e.g., when the day is the 29th, 30th, or 31st,
-- which may not exist for some months).
SET @i = 0
-- We start by looping forward, from @CurrNextRunDate to @CurrMaxDate
WHILE DATEADD(mm, @i * @CurrRunEveryXMonths, @CurrNextRunDate) <= @CurrMaxDate
BEGIN
-- Check that we need to record the calculated date.
IF DATEADD(mm, @i * @CurrRunEveryXMonths, @CurrNextRunDate) >= @CurrMinDate
BEGIN
-- Validate that the day to run exists for the month.
IF (@CurrDayOfMonthToRun > 28
AND DATEPART(DAY, DATEADD(mm, @i * @CurrRunEveryXMonths, @CurrNextRunDate)) = @CurrDayOfMonthToRun)
OR @CurrDayOfMonthToRun <= 28
BEGIN
INSERT INTO #JobScheduledDates
(job_id,
schedule_name,
RunDate,
StartTimeEx)
SELECT @CurrJobID,
@CurrScheduleName,
DATEADD(mm, @i * @CurrRunEveryXMonths, @CurrNextRunDate),
-- The format for active_start_time is hhnnss
-- and h, n, and s can be missing (if zero).
-- In other words, we need to normalize all start times to 6 chars
-- before we can start processing them efficiently.
REPLICATE('0',
6
- LEN(LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))))
+ LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))
WHERE NOT EXISTS ( SELECT TOP 1
*
FROM #JobScheduledDates
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName
AND RunDate = DATEADD(mm,
@i
* @CurrRunEveryXMonths,
@CurrNextRunDate) )
END
END
SET @i = @i + 1
END
-- Now, loop backwards from @CurrNextRunDate to @CurrMinDate.
SET @i = 0
WHILE DATEADD(mm, -@i * @CurrRunEveryXMonths, @CurrNextRunDate) >= @CurrMinDate
BEGIN
-- Check that we need to record the calculated date.
IF DATEADD(mm, -@i * @CurrRunEveryXMonths, @CurrNextRunDate) <= @CurrMaxDate
BEGIN
-- Validate that the day to run exists for the month.
IF (@CurrDayOfMonthToRun > 28
AND DATEPART(DAY, DATEADD(mm, -@i * @CurrRunEveryXMonths, @CurrNextRunDate)) = @CurrDayOfMonthToRun)
OR @CurrDayOfMonthToRun <= 28
BEGIN
INSERT INTO #JobScheduledDates
(job_id,
schedule_name,
RunDate,
StartTimeEx)
SELECT @CurrJobID,
@CurrScheduleName,
DATEADD(mm, -@i * @CurrRunEveryXMonths, @CurrNextRunDate),
-- The format for active_start_time is hhnnss
-- and h, n, and s can be missing (if zero).
-- In other words, we need to normalize all start times to 6 chars
-- before we can start processing them efficiently.
REPLICATE('0',
6
- LEN(LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))))
+ LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))
WHERE NOT EXISTS ( SELECT TOP 1
*
FROM #JobScheduledDates
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName
AND RunDate = DATEADD(mm,
-@i
* @CurrRunEveryXMonths,
@CurrNextRunDate) )
END
END
SET @i = @i + 1
END
-- Move on to the next monthly schedule for the same job
SELECT @CurrScheduleName = MIN(schedule_name)
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND schedule_name > @CurrScheduleName
AND freq_type = 16
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL
AND next_run_date > 0
END
-- Get the next job with a monthly (fixed) schedule
SELECT @CurrJobID = MIN(job_id)
FROM #JobScheduleInfo
WHERE freq_type = 16
AND job_id > @CurrJobID
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL
AND next_run_date > 0
END
/* ==========================================================================
Step 2.4 - monthly (relative) jobs - freq_type = 32 (freq_subday_type = 1, 4, 8)
This case is divided into two options:
1. A job runs every X months, at the 1st, 2nd, 3rd, 4th or last day
(e.g., Sun, Mon, etc.) day of the month. Here, the day is fixed and
is associated with a day code (1-Sun, 2-Mon, ..., 7-Sat).
2. A job runs every X months, at the 1st, 2nd, 3rd, 4th or last
day of the month, weekday, or weekend day. This is a little tricky
since the last two (weekday or weekend day) depend on the datefirst
settings on the server.
The logic here is as before- we find the next_run_date, then calculate
the previous and next run dates that fall in the observed time window,
and record all dates in #JobScheduledDates.
========================================================================== */
DECLARE @CurrCalcRunDate DATETIME
DECLARE @CurrMonth INT
DECLARE @CurrYear INT
DECLARE @CurrDayCode INT
DECLARE @CurrDayCodeFor1stOfMonth INT
DECLARE @CurrDateOfFirstYthDayOfMonth DATETIME
DECLARE @CurrFirstOfMonth DATETIME
DECLARE @CurrLastOfMonth DATETIME
DECLARE @CurrScheduledDate DATETIME
DECLARE @j INT
DECLARE @k INT
DECLARE @tmpDate DATETIME
-- Type 1: Algorithm to get the next/prev scheduled run date.
-- @CurrDayToRun:
-- 1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, 7 = Saturday
-- 8 = Day, 9 = Weekday, 10 = Weekend day
DECLARE @CurrDayToRun INT
-- @CurrRelativeInterval:
-- 1 = First, 2 = Second, 4 = Third, 8 = Fourth, 16 = Last
DECLARE @CurrRelativeInterval INT
-- Examples:
-- If @CurrDayToRun = 1 AND @CurrRelativeInterval = 4
-- the job runs on the third Sunday every X months.
-- If @CurrDayToRun = 9 AND @CurrRelativeInterval = 16
-- the job runs on the last weekday of the month, every X months.
-- We loop for each job_id and each schedule_name.
SELECT @CurrJobID = MIN(job_id)
FROM #JobScheduleInfo
WHERE freq_type = 32
-- If next_run_date is 0 or NULL for all the schedules for the job,
-- then there are no deterministic parameters and we cannot guarantee
-- the accuracy of the info so we do not address this case.
AND next_run_date IS NOT NULL
AND next_run_date > 0
WHILE @CurrJobID IS NOT NULL
BEGIN
SELECT @CurrScheduleName = MIN(schedule_name)
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND freq_type = 32
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL
AND next_run_date > 0
WHILE @CurrScheduleName IS NOT NULL
BEGIN
SELECT @CurrActiveStartTime = active_start_time,
@CurrDayToRun = freq_interval,
@CurrNextRunDate = CAST(CAST(next_run_date AS NVARCHAR(8)) AS DATETIME),
@CurrRunEveryXMonths = freq_recurrence_factor,
@CurrRelativeInterval = freq_relative_interval,
@CurrMinDate = min_datetime_to_consider,
@CurrMaxDate = max_datetime_to_consider
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName
-- Record all dates between @CurrMinDate and @CurrMaxDate
-- in which the job is scheduled to run. This is done as follows:
-- Starting with the next_run_date, we add/subtract @CurrRunsEveryXMonths
-- for each day in which the job is scheduled to run, until
-- we fill all dates between @CurrMinDate and @CurrMaxDate.
-- The key challenge here is to find the dates in which the job is scheduled
-- to run. We do this on a case by case basis (according to the @CurrDayToRun
-- and @CurrRelativeInterval. Only after finding the appropriate schedule we check
-- if the dates found fall in the time window.
-- First, we subtract from @CurrNextRunDate to move our starting point
-- to be earlier than @CurrMinDate.
SET @i = 0
WHILE DATEADD(mm, @i * @CurrRunEveryXMonths, @CurrNextRunDate) >= CAST(CAST(DATEPART(MONTH, @CurrMinDate) AS VARCHAR(2))
+ '/1/' + CAST(DATEPART(YEAR, @CurrMinDate) AS VARCHAR(4)) AS DATETIME)
SET @i = @i - 1
-- Componesate for the last redundant subtraction.
IF @i < 0
SET @i = @i + 1
-- At this point we are ready to loop and see which dates fall between @CurrMinTime and @CurrMaxTime.
-- The while checks that we have not passed the last day in the month of @CurrMaxDate.
WHILE DATEADD(mm, @i * @CurrRunEveryXMonths, @CurrNextRunDate) <= DATEADD(dd, -1,
DATEADD(mm, 1,
CAST(CAST(DATEPART(MONTH,
@CurrMaxDate) AS VARCHAR(2))
+ '/1/'
+ CAST(DATEPART(YEAR,
@CurrMaxDate) AS VARCHAR(4)) AS DATETIME)))
BEGIN
-- From here on, we have an algorithm to determine the scheduled dates
-- for the job in the examined month.
-- Get the next/prev month.
SET @CurrMonth = DATEPART(MONTH, DATEADD(mm, @i * @CurrRunEveryXMonths, @CurrNextRunDate))
SET @CurrYear = DATEPART(YEAR, DATEADD(mm, @i * @CurrRunEveryXMonths, @CurrNextRunDate))
-- Get the date for the first of the month.
SET @CurrFirstOfMonth = CAST(CAST(@CurrMonth AS VARCHAR(2)) + '/1/'
+ CAST(@CurrYear AS VARCHAR(4)) AS DATETIME)
-- Get the date for the last day of the month.
SET @CurrLastOfMonth = DATEADD(dd, -1,
DATEADD(mm, 1,
CAST(CAST(@CurrMonth AS VARCHAR(2)) + '/1/'
+ CAST(@CurrYear AS VARCHAR(4)) AS DATETIME)))
IF @CurrDayToRun BETWEEN 1 AND 7
BEGIN
-- Get the day code for the first day of the month.
SET @CurrDayCodeFor1stOfMonth = CASE DATENAME(dw, @CurrFirstOfMonth)
WHEN 'Sunday' THEN 1
WHEN 'Monday' THEN 2
WHEN 'Tuesday' THEN 3
WHEN 'Wednesday' THEN 4
WHEN 'Thursday' THEN 5
WHEN 'Friday' THEN 6
WHEN 'Saturday' THEN 7
END
-- Get the date of the first encounter of the day in @CurrDayToRun for the month.
SET @CurrDateOfFirstYthDayOfMonth = DATEADD(dd,
(@CurrDayToRun + 7
- @CurrDayCodeFor1stOfMonth) % 7,
@CurrFirstOfMonth)
-- Get the date in which the job is scheduled to run.
SET @CurrScheduledDate = CASE @CurrRelativeInterval
WHEN 1 THEN @CurrDateOfFirstYthDayOfMonth
WHEN 2 THEN DATEADD(dd, 7, @CurrDateOfFirstYthDayOfMonth)
WHEN 4 THEN DATEADD(dd, 2 * 7, @CurrDateOfFirstYthDayOfMonth)
WHEN 8 THEN DATEADD(dd, 3 * 7, @CurrDateOfFirstYthDayOfMonth)
-- Last day Y of the month.
-- There are no more than 5 encounters of day Y per month.
-- The last day Y can therefore be DATEADD(dd, 3*7, @CurrDateOfFirstYthDayOfMonth)
-- or DATEADD(dd, 4*7, @CurrDateOfFirstYthDayOfMonth).
WHEN 16
THEN CASE WHEN DATEPART(MONTH,
DATEADD(dd, 4 * 7,
@CurrDateOfFirstYthDayOfMonth)) <> @CurrMonth
THEN DATEADD(dd, 3 * 7,
@CurrDateOfFirstYthDayOfMonth)
ELSE DATEADD(dd, 4 * 7,
@CurrDateOfFirstYthDayOfMonth)
END
END
END
ELSE
BEGIN
-- This is the case where @CurrDayToRun is between 8 and 10.
IF @CurrDayToRun = 8 -- Job runs on the 1st - 4th or last day of month.
BEGIN
SET @CurrScheduledDate = CASE @CurrRelativeInterval
WHEN 1 THEN @CurrFirstOfMonth
WHEN 2 THEN DATEADD(dd, 1, @CurrFirstOfMonth)
WHEN 4 THEN DATEADD(dd, 2, @CurrFirstOfMonth)
WHEN 8 THEN DATEADD(dd, 3, @CurrFirstOfMonth)
WHEN 16 THEN @CurrLastOfMonth
END
END
IF @CurrDayToRun IN (9, 10) -- Job runs on the 1st - 4th or last weekday or weekend day of the month.
BEGIN
IF @CurrRelativeInterval IN (1, 2, 4, 8)
BEGIN
SET @tmpDate = @CurrFirstOfMonth
SET @k = 0
-- @j holds the Nth encounter of a business or weekend day.
SET @j = CASE @CurrRelativeInterval
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 4 THEN 3
WHEN 8 THEN 4
END
SET @CurrScheduledDate = NULL
WHILE @CurrScheduledDate IS NULL
BEGIN
-- Here we check if we need to track week or weekend days
-- and count when they are encountered.
IF (@CurrDayToRun = 9
AND ((((@@DATEFIRST - 1) + (DATEPART(weekday, @tmpDate) - 1))
% 7) NOT IN (5, 6)))
OR (@CurrDayToRun = 10
AND ((((@@DATEFIRST - 1) + (DATEPART(weekday, @tmpDate)
- 1)) % 7) IN (5, 6)))
-- Track the fact that a day of the desired type is encountered.
SET @k = @k + 1
-- Loop to the next date unless the @tmpDate is already the Nth encounter.
IF @k < @j
SET @tmpDate = DATEADD(dd, 1, @tmpDate)
ELSE
SET @CurrScheduledDate = @tmpDate
END
END
ELSE
BEGIN
-- Here we are looking for the last business or weekend day for the month.
SET @tmpDate = @CurrLastOfMonth
SET @CurrScheduledDate = NULL
WHILE @CurrScheduledDate IS NULL
BEGIN
-- Check if the job runs on the last business day and the last date of the month
-- is indeed a business day, or if the job runs on the last weekend day
-- and the last day is indeed a weekend day
IF (@CurrDayToRun = 9
AND (((@@DATEFIRST - 1) + (DATEPART(weekday, @tmpDate) - 1))
% 7) NOT IN (5, 6))
OR (@CurrDayToRun = 10
AND (((@@DATEFIRST - 1) + (DATEPART(weekday, @tmpDate)
- 1)) % 7) IN (5, 6))
SET @CurrScheduledDate = @tmpDate
ELSE
SET @tmpDate = DATEADD(dd, -1, @tmpDate)
END
END
END
END
-- Here we have the @CurrScheduledDate - date of the month when the job
-- is scheduled to run. Check that we need to record the calculated date before we do so.
IF @CurrScheduledDate BETWEEN @CurrMinDate AND @CurrMaxDate
BEGIN
INSERT INTO #JobScheduledDates
(job_id,
schedule_name,
RunDate,
StartTimeEx)
SELECT @CurrJobID,
@CurrScheduleName,
@CurrScheduledDate,
-- The format for active_start_time is hhnnss
-- and h, n, and s can be missing (if zero).
-- In other words, we need to normalize all start times to 6 chars
-- before we can start processing them efficiently.
REPLICATE('0',
6 - LEN(LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))))
+ LTRIM(RTRIM(CAST(@CurrActiveStartTime AS VARCHAR(6))))
WHERE NOT EXISTS ( SELECT TOP 1
*
FROM #JobScheduledDates
WHERE job_id = @CurrJobID
AND schedule_name = @CurrScheduleName
AND RunDate = @CurrScheduledDate )
END
SET @i = @i + 1
END
SELECT @CurrScheduleName = MIN(schedule_name)
FROM #JobScheduleInfo
WHERE job_id = @CurrJobID
AND schedule_name > @CurrScheduleName
AND freq_type = 32
-- Same as above- the case where next_run_date is zero or null
-- is not addressed because we may conclude inaccurate results.
AND next_run_date IS NOT NULL
AND next_run_date > 0
END
SELECT @CurrJobID = MIN(job_id)
FROM #JobScheduleInfo
WHERE freq_type = 32
AND job_id > @CurrJobID
-- If next_run_date is 0 or NULL for all the schedules for the job,
-- then there are no deterministic parameters and we cannot guarantee
-- the accuracy of the info so we do not address this case.
AND next_run_date IS NOT NULL
AND next_run_date > 0
END
/* ========================================================================
Step 3 - Find the times at which the job is scheduled to run,
for all dates found in Step 2 above.
In Step 2 we found all the dates in which the job was scheduled to run.
For all those dates, we now find the scheduled times.
====================================================== */
-- In #JobScheduledDates we already recorded the first time of the day
-- in which jobs are run (column StartTimeEx). First, we update
-- that table to get the number of seconds from the start of the day
-- for the first run (column StartTimeInSecFromStartOfDay).
UPDATE #JobScheduledDates
SET StartTimeInSecFromStartOfDay = CAST(LEFT(StartTimeEx, 2) AS INT) * 3600
+ CAST(SUBSTRING(StartTimeEx, 3, 2) AS INT) * 60 + CAST(RIGHT(StartTimeEx, 2) AS INT)
-- Run times have to be constructed for all jobs that run
-- daily, weekly, or monthly. Also, if a job is scheduled
-- run once per day instead of every X minutes or hours,
-- then we already have the run time in #JobScheduledDates.
-- In order to distinguish between the run-dates and run dates & times,
-- we create a new temp table that holds the date + time info.
IF OBJECT_ID('tempdb..#JobScheduledDatesAndTimes') IS NOT NULL
DROP TABLE #JobScheduledDatesAndTimes
CREATE TABLE #JobScheduledDatesAndTimes (job_id VARCHAR(64),
schedule_name NVARCHAR(128),
RunDateTime DATETIME,
FirstRunAfterRunDateTime DATETIME,
StartStepID INT)
-- The scheduled times are determined as follows (for daily, weekly, and monthly jobs):
-- freq_subday_type:
-- 1 - run once (not interesting. we already have the run-time for this case).
-- 4 - run every X minutes. X is stored in freq_subday_interval.
-- 8 - run every Y hours. Y is stored in freq_subday_interval.
-- It is now left to calculate the run times.
-- We start by populating #JobScheduledDatesAndTimes with all jobs
-- that run once.
INSERT INTO #JobScheduledDatesAndTimes WITH (TABLOCK)
(job_id,
schedule_name,
RunDateTime)
SELECT b.job_id,
b.schedule_name,
DATEADD(ss, b.StartTimeInSecFromStartOfDay, b.RunDate)
FROM #JobScheduleInfo a
INNER JOIN #JobScheduledDates b ON a.job_id = b.job_id
AND a.schedule_name = b.schedule_name
WHERE a.freq_subday_type NOT IN (4, 8)
AND DATEADD(ss, b.StartTimeInSecFromStartOfDay, b.RunDate) >= a.min_datetime_to_consider
AND DATEADD(ss, b.StartTimeInSecFromStartOfDay, b.RunDate) <= a.max_datetime_to_consider
CREATE UNIQUE CLUSTERED INDEX JobScheduledDatesAndTimes ON #JobScheduledDatesAndTimes (job_id, schedule_name, RunDateTime)
-- Next, we handle all jobs that are scheduled to run every X minutes in a day.
-- Here, we simply add @i x X minutes for all schedules at once until
-- we cover the entire day.
DECLARE @Cnt INT
SET @i = 0
SET @Cnt = 1
WHILE @Cnt > 0
BEGIN
INSERT INTO #JobScheduledDatesAndTimes
(job_id,
schedule_name,
RunDateTime)
SELECT b.job_id,
b.schedule_name,
DATEADD(ss, @i * 60 * a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate)
FROM #JobScheduleInfo a
INNER JOIN #JobScheduledDates b ON a.job_id = b.job_id
AND a.schedule_name = b.schedule_name
WHERE a.freq_subday_type = 4
-- We need to make sure that we don't cross over to the next day.
AND DATEPART(DAY,
DATEADD(ss, @i * 60 * a.freq_subday_interval + b.StartTimeInSecFromStartOfDay,
b.RunDate)) = DATEPART(DAY, b.RunDate)
-- Also consider the min/max_datetime_to_consider
AND DATEADD(ss, @i * 60 * a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate) >= a.min_datetime_to_consider
AND DATEADD(ss, @i * 60 * a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate) <= a.max_datetime_to_consider
SET @Cnt = @@ROWCOUNT
SET @i = @i + 1
END
-- Finally, we do the same as above for all jobs that run every Y hours.
SET @i = 0
SET @Cnt = 1
WHILE @Cnt > 0
BEGIN
INSERT INTO #JobScheduledDatesAndTimes
(job_id,
schedule_name,
RunDateTime)
SELECT b.job_id,
b.schedule_name,
DATEADD(ss, @i * 3600 * a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate)
FROM #JobScheduleInfo a
INNER JOIN #JobScheduledDates b ON a.job_id = b.job_id
AND a.schedule_name = b.schedule_name
WHERE a.freq_subday_type = 8
-- We need to make sure that we don't cross over to the next day.
AND DATEPART(DAY,
DATEADD(ss, @i * 3600 * a.freq_subday_interval + b.StartTimeInSecFromStartOfDay,
b.RunDate)) = DATEPART(DAY, b.RunDate)
-- Also consider the min/max_datetime_to_consider
AND DATEADD(ss, @i * 3600 * a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate) >= a.min_datetime_to_consider
AND DATEADD(ss, @i * 3600 * a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate) <= a.max_datetime_to_consider
SET @Cnt = @@ROWCOUNT
SET @i = @i + 1
END
-- Update the step ID
UPDATE a
SET StartStepID = b.start_step_id
FROM #JobScheduledDatesAndTimes a
INNER JOIN #JobScheduleInfo b ON a.job_id = b.job_id
AND a.schedule_name = b.schedule_name
-- Get the first run from sysjobhistory after the recorded RunDateTime
UPDATE a
SET FirstRunAfterRunDateTime = (SELECT MIN(DATEADD(ss,
CAST(b.run_time / 10000 AS INT) * 3600
+ (CAST(b.run_time / 100 AS INT) % 100) * 60 + (b.run_time % 100),
CAST(CAST(b.run_date AS VARCHAR(8)) AS DATETIME)))
FROM msdb.dbo.sysjobhistory b
WHERE b.job_id = a.job_id
AND b.step_id = a.StartStepID
AND a.RunDateTime <= DATEADD(ss,
CAST(b.run_time / 10000 AS INT) * 3600
+ (CAST(b.run_time / 100 AS INT) % 100) * 60
+ (b.run_time % 100),
CAST(CAST(b.run_date AS VARCHAR(8)) AS DATETIME)))
FROM #JobScheduledDatesAndTimes a
/* ========================================================================
Step 4 - Find missed jobs - jobs that were supposed to run but did
not fire at the schedule found in #JobScheduledDatesAndTimes.
Here, we compare the schedule found in #JobScheduledDatesAndTimes
with the information in sysjobhistory and check if the job
did not fire in any of the found dates and times.
Since the job may start with a small delay, we look for jobs
that did not start on time and up to X seconds from that time.
X is stored as the variable @TimeGap.
====================================================== */
-- Get all missed jobs (for all job schedules)
SELECT a.job_id AS JobID,
(SELECT TOP 1 job_name FROM #JobScheduleInfo WHERE job_id = a.job_id) AS JobName,
a.schedule_name AS ScheduleName,
a.RunDateTime AS MissedSchedule,
a.FirstRunAfterRunDateTime AS FirstJobRunAfterMissedSchedule
FROM #JobScheduledDatesAndTimes a
WHERE (a.FirstRunAfterRunDateTime IS NOT NULL
AND a.RunDateTime < DATEADD(ss, -@TimeSlackGapInSeconds, a.FirstRunAfterRunDateTime))
OR (a.FirstRunAfterRunDateTime IS NULL
AND a.RunDateTime <= GETDATE())
-- Get most recent missed jobs (one per job)
SELECT DISTINCT
job_id AS JobID,
(SELECT TOP 1 job_name FROM #JobScheduleInfo WHERE job_id = RS.job_id) AS JobName,
(SELECT TOP 1
schedule_name
FROM #JobScheduledDatesAndTimes
WHERE job_id = RS.job_id
AND RunDateTime = RS.MaxRunDateTime) AS ScheduleName,
MaxRunDateTime AS LastMissedSchedule,
(SELECT TOP 1
FirstRunAfterRunDateTime
FROM #JobScheduledDatesAndTimes
WHERE job_id = RS.job_id
AND RunDateTime = RS.MaxRunDateTime) AS FirstJobRunAfterLastMissedSchedule
FROM (SELECT job_id,
MAX(RunDateTime) AS MaxRunDateTime
FROM #JobScheduledDatesAndTimes
WHERE (FirstRunAfterRunDateTime IS NOT NULL
AND RunDateTime < DATEADD(ss, -@TimeSlackGapInSeconds, FirstRunAfterRunDateTime))
OR (FirstRunAfterRunDateTime IS NULL
AND RunDateTime <= GETDATE())
GROUP BY job_id) RS
-- Cleanup - drop temp tables used in the script.
IF OBJECT_ID('tempdb..#JobScheduleInfo') IS NOT NULL
DROP TABLE #JobScheduleInfo
IF OBJECT_ID('tempdb..#xp_results') IS NOT NULL
DROP TABLE #xp_results
IF OBJECT_ID('tempdb..#JobScheduledDates') IS NOT NULL
DROP TABLE #JobScheduledDates
IF OBJECT_ID('tempdb..#DaysAndDates') IS NOT NULL
DROP TABLE #DaysAndDates
IF OBJECT_ID('tempdb..#JobScheduledDatesAndTimes') IS NOT NULL
DROP TABLE #JobScheduledDatesAndTimes