How do I monitor & exclude for a SQL Agent job that only runs on a specific run schedule?

I monitor currently running jobs for time exceeded, not completed jobs (I do report on completed jobs when necessary), so my approach is somewhat different.  I'll have to think through how to handle your specific case.

OK. Thank you very much for looking into my case. This one has me stumped. I've flirted with some ways to maybe declare a couple of datetime fields (i.e.  @starttime & @endtime) & then hard coding those to whatever start (ex. 1am) & end times (ex. 7pm) but i can never get that to work right for me. Maybe? Confused on how to tie that into my current monitoring.

The really tricky part is that a job could have multiple schedules.  If you're willing to restrict it to considering a single job schedule, it becomes vastly easier to check.

Ahh OK. Understood. Yeah at this point if i could even have a solution for a single job schedule that would be great. Way further than i've been able to figure out.  :) Thx again.

If i need to supply any other supporting information let me know. Failed to mention that the only reason i have those (2) job exceptions:
'A360_EOD_SB',
'A360 Report Table Refresh Monitor'
in my DB view is because i was holding them there until i could figure out how to isolate & monitor them based on their unique job schedules. The other jobs in that view 'where j.name not in...' are processing jobs that are run manually and/or are weekly/monthly run jobs so i don't care to monitor them anyway. Just figured i better point that out as you continue to work this case. Thx.

This code will create a table of upcoming runs -- for the current calendar day (or however many days you want) of every job in SQLPERF.dbo.R360ProcessingJobs.  You can then use that table so see if the job is supposed to be running during the current time or not.  [If the table takes too long to build, note that unless you change the job's schedule, the upcoming runs won't change, so you could instead create a more permanent 'jobs_upcoming' table and refresh it only every, say, 12 hours.]


IF OBJECT_ID('tempdb.dbo.#jobs_upcoming') IS NOT NULL
    DROP TABLE #jobs_upcoming
CREATE TABLE #jobs_upcoming (
    job_name sysname NOT NULL,
    UpcomingRunDateTime datetime NULL,
    UNIQUE CLUSTERED ( job_name, UpcomingRunDateTime )
    )

DECLARE @startdate datetime
DECLARE @numberOfCalendarDays int
DECLARE @currTimeSeconds int

SET @startdate = DATEADD(HOUR, -1, GETDATE()) --RunDateTime
SET @numberOfCalendarDays = 1
SET @currTimeSeconds = DATEDIFF(SECOND, 0, DATEADD(DAY, -DATEDIFF(DAY, 0, @startDate), @startDate))

INSERT INTO #jobs_upcoming ( job_name, UpcomingRunDateTime )
SELECT
    job_name,
    DATEADD(SECOND, CASE WHEN s.freq_subday_type = 1 THEN s.active_start_seconds ELSE
    s.active_start_seconds + (jobPerDay.tally * s.calc_subday_factor * s.freq_subday_interval) END,
    DATEADD(DAY, jobDay.tally, CONVERT(CHAR(8), @startDate, 112))) AS UpcomingRunDateTime
FROM (
    SELECT rpj1.job_name, s1.schedule_id, s1.freq_type, s1.freq_interval, s1.freq_subday_type, s1.freq_subday_interval,
    CASE s1.freq_subday_type WHEN 8 THEN 3600 WHEN 4 THEN 60 WHEN 2 THEN 1 ELSE 999999 END AS
        calc_subday_factor,
    s1.freq_relative_interval, s1.freq_recurrence_factor,
    s1.name,
    CAST(CAST(s1.active_start_date AS CHAR(8)) AS DATETIME) AS active_start_datetime,
    CAST(CAST(s1.active_end_date AS CHAR(8)) AS DATETIME) AS active_end_datetime,
    CASE SplitScheds.SchedCode WHEN '2' THEN
            ((((s1.active_start_time / 10000 * 3600) + (s1.active_start_time % 10000 / 100 * 60) +
        s1.active_start_time % 100) +
        (CEILING((86400.0 -
            ((s1.active_start_time / 10000 * 3600) + (s1.active_start_time % 10000 / 100 * 60) +
        s1.active_start_time % 100)) /
        ((CASE s1.freq_subday_type WHEN 8 THEN 3600 WHEN 4 THEN 60 ELSE 1 END) * s1.freq_subday_interval)) /* + 1 */) *
        ((CASE s1.freq_subday_type WHEN 8 THEN 3600 WHEN 4 THEN 60 ELSE 1 END) * s1.freq_subday_interval) ))
        - 86400
    ELSE
            (s1.active_start_time / 10000 * 3600) + (s1.active_start_time % 10000 / 100 * 60) +
        s1.active_start_time % 100
    END AS active_start_seconds,
    CASE SplitScheds.SchedCode WHEN '1' THEN 23 * 3600 + 59 * 60 + 59 ELSE
            (s1.active_end_time / 10000 * 3600) + (s1.active_end_time % 10000 / 100 * 60) +
        s1.active_end_time % 100
    END AS active_end_seconds,
      ' Between ' + CASE WHEN active_start_time < 10000  THEN '12' ELSE RIGHT('0' + CAST(active_start_time / 10000 -
            CASE WHEN active_start_time / 10000 > 12 THEN 12 ELSE 0 END AS varchar(2)), 2) END + ':' +
          RIGHT('0' + CAST(active_start_time % 10000 / 100 AS varchar(2)), 2) + ':' +
          RIGHT('0' + CAST(active_start_time % 100 AS varchar(2)), 2) +                  
            CASE WHEN active_start_time / 10000 >= 12 THEN 'pm' ELSE 'am' END +
      ' And ' + CASE WHEN active_end_time < 10000  THEN '12' ELSE RIGHT('0' + CAST(active_end_time / 10000 -
            CASE WHEN active_end_time / 10000 > 12 THEN 12 ELSE 0 END AS varchar(2)), 2) END + ':' +
          RIGHT('0' + CAST(active_end_time % 10000 / 100 AS varchar(2)), 2) + ':' +
          RIGHT('0' + CAST(active_end_time % 100 AS varchar(2)), 2) +                  
            CASE WHEN active_end_time / 10000 >= 12 THEN 'pm' ELSE 'am' END
      AS SchedTimeRange
    FROM msdb.dbo.sysschedules s1 WITH (NOLOCK)
    INNER JOIN msdb.dbo.sysjobschedules js1 ON
        js1.schedule_id = s1.schedule_id
    INNER JOIN msdb.dbo.sysjobs j1 ON
        j1.job_id = js1.job_id
    INNER JOIN SQLPERF.dbo.R360ProcessingJobs rpj1 ON
        rpj1.job_name = j1.name
    INNER JOIN (
        SELECT '0' AS SchedCode UNION ALL SELECT '1' UNION ALL SELECT '2'
    ) AS SplitScheds ON
          (s1.active_start_time <= s1.active_end_time AND SplitScheds.SchedCode = '0') OR
          (s1.active_start_time > s1.active_end_time AND SplitScheds.SchedCode IN ('1', '2'))
    WHERE        
        (s1.enabled = 1)
) AS s
INNER JOIN DBA.dbo.tally jobDay WITH (NOLOCK) ON jobDay.tally BETWEEN 0 AND (CASE WHEN s.freq_type = 1 THEN
        CASE WHEN @startDate BETWEEN s.active_start_datetime AND DATEADD(SECOND, -1, DATEADD(DAY, 1, s.active_start_datetime))
             THEN 0 ELSE -1 END
        ELSE @numberOfCalendarDays - 1 END) AND
    1 = CASE WHEN s.freq_type = 1 THEN 1
             WHEN s.freq_type = 4 THEN CASE WHEN s.freq_interval = 1 THEN 1 ELSE
                 CASE WHEN DATEDIFF(DAY, s.active_start_datetime, DATEADD(DAY, jobDay.tally, @startDate))
                     % s.freq_interval = 0 THEN 1 ELSE 0 END END
             WHEN s.freq_type = 8 THEN
                 CASE WHEN s.freq_interval = 127 THEN 1 ELSE
                 CASE DATENAME(WEEKDAY, DATEADD(DAY, jobDay.tally, @startDate))                
                 WHEN 'Sunday'    THEN CASE WHEN s.freq_interval & 1 > 0 THEN 1 ELSE 0 END
                 WHEN 'Monday'    THEN CASE WHEN s.freq_interval & 2 > 0 THEN 1 ELSE 0 END
                 WHEN 'Tuesday'   THEN CASE WHEN s.freq_interval & 4 > 0 THEN 1 ELSE 0 END
                 WHEN 'Wednesday' THEN CASE WHEN s.freq_interval & 8 > 0 THEN 1 ELSE 0 END
                 WHEN 'Thursday'  THEN CASE WHEN s.freq_interval & 16 > 0 THEN 1 ELSE 0 END
                 WHEN 'Friday'    THEN CASE WHEN s.freq_interval & 32 > 0 THEN 1 ELSE 0 END
                 WHEN 'Saturday'  THEN CASE WHEN s.freq_interval & 64 > 0 THEN 1 ELSE 0 END END END
             WHEN s.freq_type = 16 THEN CASE WHEN DAY(DATEADD(DAY, jobDay.tally, @startDate)) =
                     s.freq_interval THEN 1 ELSE 0 END
             WHEN s.freq_type = 32 THEN CASE WHEN EXISTS(SELECT 1 FROM msdb.dbo.sysjobschedules js1 WITH (NOLOCK)
                 WHERE js1.schedule_id = s.schedule_id AND
                     js1.next_run_date = CONVERT(CHAR(8), DATEADD(DAY, jobDay.tally, @startDate), 112))                        
                 THEN 1 ELSE 0 END
        ELSE 0
        END            
INNER JOIN DBA.dbo.tally jobPerDay WITH (NOLOCK) ON
    s.active_start_datetime <= @startDate AND
    s.active_end_datetime >= DATEADD(DAY, jobDay.tally, @startDate) AND
    jobPerDay.tally BETWEEN CASE
        WHEN jobDay.tally > 0 OR @currTimeSeconds <= s.active_start_seconds THEN 0
        WHEN @currTimeSeconds >= s.active_end_seconds THEN 999999
        ELSE CEILING (((@currTimeSeconds - s.active_start_seconds) * 1.00) /
            (s.calc_subday_factor * CASE WHEN s.freq_subday_interval = 0 THEN 1 ELSE s.freq_subday_interval END)) END
    AND CASE WHEN s.freq_subday_type IN (0, 1) THEN 0 ELSE
        ((s.active_end_seconds - s.active_start_seconds) /
            (s.calc_subday_factor * CASE WHEN s.freq_subday_interval = 0 THEN 1
        ELSE s.freq_subday_interval END)) END

--SELECT * FROM #jobs_upcoming

--TEST Query for the above process that i've used to test OUTSIDE of the SQL Agent job

--include/copy code above to create the 'upcoming_jobs' table

if
(
SELECT rpj.JobName
--SELECT count(*)
FROM SQLPERF.dbo.R360ProcessingJobs rpj  --view
WHERE rpj.RunDateTime < DATEADD(MI, -15, GETDATE())
     AND EXISTS(SELECT 1 FROM #jobs_upcoming ju WHERE ju.job_name = rpj.job_name AND
         ju.UpcomingRunDateTime BETWEEN DATEADD(MINUTE, -10, GETDATE()) AND DATEADD(MI, +15, GETDATE()))
) > 0
print 'TEST: Account360 Processing Jobs have not been run in last 15 mins. Send email.'
else print 'TEST: All is clear.'

Hello Scott. Apologies i was out of town unexpectedly since Friday, so just got a chance to review your response from 12/19. Anyway, i was going to test out your suggestion locally but having issues with the script, particularly in regards to the creation & use of this object (dbo.tally??). Can't seem to find where it comes into play or how/when it was created within the script. Its being used as part of the first select & then later as part of some of the JOINS. Can you elaborate on how this object is supposed to be used?

Msg 208, Level 16, State 1, Line 17
Invalid object name 'DBA.dbo.tally'.

INSERT INTO #jobs_upcoming ( job_name, UpcomingRunDateTime )
SELECT
    job_name,
    DATEADD(SECOND, CASE WHEN s.freq_subday_type = 1 THEN s.active_start_seconds ELSE
    s.active_start_seconds + (jobPerDay.tally * s.calc_subday_factor * s.freq_subday_interval) END,
    DATEADD(DAY, jobDay.tally, CONVERT(CHAR(8), @startDate, 112))) AS UpcomingRunDateTime

....

INNER JOIN DBA.dbo.tally jobDay WITH (NOLOCK) ON jobDay.tally BETWEEN 0 AND (CASE WHEN s.freq_type = 1 THEN


**Sidenote: I also cleared up a reference to one of my column names from the view i create/use (R360ProcessingJobs) as your script is looking for 'job_name' and my column is 'jobname':

   INNER JOIN SQLPERF.dbo.R360ProcessingJobs rpj1 ON
        rpj1.jobname