Custom Alert - Jobs running longer than usual

Version 4

    This computes the average duration of a job from the job history tables, and computes a number representing how many times longer it is currently running than usual.

     

    For example.

    Job1 usually takes 30 minutes.  If it takes 90 minutes, it will return 3.

    It excludes jobs that take 30 minutes.  In my particular environment, that filters out some noise.

    It excludes the Ola Hallengren index maintenance job.

     

    Type: Custom SQL Alert - Multiple Numeric Return

     

    SQL Statement:

    SELECT job.name AS 'jobName',

    (DATEDIFF(SECOND,ja.start_execution_date,GetDate()) ) / (art.avgRunTime) AS TimesLonger

    FROM msdb.dbo.sysjobs AS job

        INNER JOIN

        (

            SELECT j.job_id,

                   AVG((run_duration / 10000) * 3600 + (run_duration / 100 % 100) * 60 + run_duration % 100) AS 'avgRunTime' /* convert HHMMSS to seconds */

            FROM msdb.dbo.sysjobhistory jh

                INNER JOIN msdb.dbo.sysjobs j

                    ON jh.job_id = j.job_id

            WHERE step_id = 0 -- only grab our total run-time

                  AND run_status = 1 -- only grab successful executions

                  AND msdb.dbo.agent_datetime(run_date, run_time) >= DATEADD(DAY, -30, GETDATE())

            GROUP BY j.job_id

        ) AS art

            ON job.job_id = art.job_id

        INNER JOIN msdb..sysjobactivity ja

            ON ja.job_id = job.job_id

    WHERE job.enabled = 1   /* do not display disabled jobs*/

          AND NOT EXISTS

    (    /* make sure this is the most recent run*/

        SELECT 1

        FROM msdb..sysjobactivity new

        WHERE new.job_id = ja.job_id

              AND new.start_execution_date > ja.start_execution_date

    )

    AND ja.start_execution_date IS NOT NULL

    AND ja.stop_execution_date IS NULL /* has not stopped */

    AND (DATEDIFF(SECOND,ja.start_execution_date,GetDate())) > (art.avgRunTime )

    /* Greater than 30 minutes */

    AND (DATEDIFF(SECOND,ja.start_execution_date,GetDate())) > 1800

    AND job.Name <> 'Maintenance | IndexOptimize - USER_DATABASES'

     

    Medium level - Min: 3 Max: 5

    High level - Min: 5 Max: Empty