Solved

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

Posted on 2014-12-19
9
86 Views
Last Modified: 2015-03-19
Hello everyone. I'm new to this site so please bear with me. Got recommended to this site by a colleague of mine who raves about it. Anyway, i have a set of SQL Agent jobs that one of our clients wants me to monitor for as they are daily processing jobs for their day to day business. These particular SQL jobs run every 1 minute throughout the day, 24/7. Which is fine & i've been able to create a DB view & SQL monitoring job that i use to track the activity of those jobs with a threshold of 15 mins. So I'm looking at job activity to ensure these specific jobs do not run over 15 mins or hadn't run in the last 15 mins for whatever reason, otherwise i deem them to be exceptions and i send out a notification to our DBA support team to act accordingly. My issue lies with a new set of these daily processing jobs that don't fall in that 24/7 runtime but have their own isolated schedules. One of them runs every 1 minute but ONLY between 1am - 7pm. And another only runs every 1 min but ONLY from 9am - 5pm each day.

Been searching around but can't seem to find a solution that will allow me to continue my regular monitoring for the other processing jobs i need to monitor around the clock but also (hopefully in the same job or view), isolate the above mentioned exceptions as i don't want to receive notification emails during those specific times for those (2) specific jobs. I'm getting false alerts for those two jobs otherwise during their off peak times. Can anyone assist me? I have attached a file with the logic for both my DB view and the SQL job logic i use that calls that view.
ExpertsExchange-Info-20141219.txt
0
Comment
Question by:Claude Johnson
  • 5
  • 4
9 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40509576
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.
0
 

Author Comment

by:Claude Johnson
ID: 40509605
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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40509860
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.
0
 

Author Comment

by:Claude Johnson
ID: 40509874
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.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:Claude Johnson
ID: 40510081
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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40510115
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
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40510120
--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.'
0
 

Author Comment

by:Claude Johnson
ID: 40515580
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
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40515694
Sorry.  "Tally" is a "standard" tally table, i.e., one row each for a list of numbers from 0 to 1,000,000 (or whatever size).

You can create it like so:
CREATE TABLE dbo.tally ( tally int, CONSTRAINT tally__CL UNIQUE CLUSTERED ( tally ) )

;WITH
cteTally10 AS (
    SELECT 0 AS tally UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
    SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
cteTally100 AS (
    SELECT 1 AS tally
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
),
cteTally10K AS (
    SELECT 1 AS tally
    FROM cteTally100 c1
    CROSS JOIN cteTally100 c2
),
cteTally1Mil AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.tally) - 1 AS tally
    FROM cteTally100 c1
    CROSS JOIN cteTally10K c2
)
INSERT INTO dbo.tally
SELECT tally
FROM cteTally1Mil
ORDER BY tally
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now