I suggest you ...

specify a time in which jobs should be finished by

It would be helpful if for jobs you can specify a time in which they should be finished by and if they overun that time then an email alert is sent out to notify you.

8 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    SteveSteve shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    2 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • StephenStephen commented  ·   ·  Flag as inappropriate

        Custom Metrics are OK for low-volumes of Jobs and servers, but as each and every Custom Metric of this kind cites ONE and ONLY ONE Job, and the Alert must, therefore, be named coincident with the Metric, it is not a long term viable option.

        I have used a technique SIMILAR to what Fiona describes but using an excessive DURATION and, while it does work, it's not something I look forward to maintaining/reproducing across instances/jobs.

        Please consider, as Steve requested, ENHANCING SQL Monitor so there is a built-in Alert that one can pick the Job Name and state EITHER the duration(s) from the Start-time after which a Low, Medium or High Alert must be emitted, or the exact time that the Job must be complete by.

        SQL Monitor Forum Post http://www.red-gate.com/MessageBoard/viewtopic.php?p=57950#57950 details the scenario and query.

      • Fiona GazeleyAdminFiona Gazeley (Admin, Red Gate Software) commented  ·   ·  Flag as inappropriate

        Hi Steve

        Thanks for your idea. This is exactly the type of thing we have introduced Custom Metrics for in SQL Monitor version 3.0 (http://www.red-gate.com/monitor).
        The idea behind Custom Metrics is that you can measure things that are important to you.
        To show you how it might work I have put together some T-SQL that would do the job. I'm sure it can be improved or be written in a nice way, but it should show the idea:

        You would create a custom metric with the following T-SQL.
        It will return 0 is a job hasn't started, if it's completed, or if it's running within the expected timescales. It will return 1 if it's still running and outside the expected timescales.

        You would need to edit the T-SQL to contain the name of your job and the expected start and finish hours / minutes.

        -- Code to examine if a job has overrun to a specific time
        DECLARE @todaysdate DATETIME, @job_name NVARCHAR(50),
        @expected_start_hour INT, @expected_start_minute INT,
        @expected_finish_hour INT, @expected_finish_minute INT

        SELECT @todaysdate = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())),
        -- ******************************************************
        -- FILL IN DETAILS SPECIFIC TO YOUR JOB
        -- ******************************************************
        @job_name = 'TestJob',
        @expected_start_hour = 17,
        @expected_start_minute = 0,
        @expected_finish_hour = 17,
        @expected_finish_minute = 2
        -- Expected outcomes
        -- 0=job in expected timescales
        -- 1=job has overrun

        SELECT COUNT(*) AS JobOverRunning
        FROM msdb..sysjobs j
        WHERE
        j.name = @job_name
        AND EXISTS
        (
        SELECT TOP 1 *
        FROM msdb..sysjobsteps sj
        INNER JOIN msdb..sysjobactivity ja ON ja.job_id = j.job_id
        WHERE sj.job_id = j.job_id
        AND ja.start_execution_date >= DATEADD(mi, @expected_start_minute, DATEADD(hh, @expected_start_hour, @todaysdate))
        AND ja.stop_execution_date IS NULL -- only include stuff that is still running
        AND GETDATE() >= DATEADD(mi, @expected_finish_minute, DATEADD(hh, @expected_finish_hour, @todaysdate))
        ORDER BY start_execution_date
        )

        In this example the job is called "TestJob" and it starts at 17:00 (5pm) and is expected to be complete by 17:02 (5.02pm).

        You would then add an alert on this job which would be raised if the value goes *above* 0 (I.e. it will alert when the job returns 1). This indicates it's gone over the expected time-scales.

        I hope that gives you some idea of the types of things that can be done using custom metrics.

        Best regards, Fiona

      Feedback and Knowledge Base