Capture SQL jobs running longer than x minutes

  • Feivel

    SSCertifiable

    Points: 7486

    I am trying to work on a solution that would capture SQL jobs in a table that ran longer than say x minutes (it can be configurable) during the entire course of the day based on the job history and dump the result set into a table that I can loop through during the start of my day that would help me figure out what SQL job ran longer. One important thing here would be if a job runs just once then it won't be an issue but if it runs multiple times during the day say every hr. then I would only like to dump the execution that took longer. I tried to google for the solution but I couldn't find something that fits my criteria. Any help would be much appreciated. I know for sure many of you would be doing this so it is definitely not re-inventing the wheel.

  • Grant Fritchey

    SSC Guru

    Points: 395417

    The information is captured in system tables. You just have to query those and you're off and running. Take a look at sys.jobhistory. Then, to only get the longest one, SELECT TOP 1 .... ORDER BY Duration. Ta da!

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Andrey

    SSChasing Mays

    Points: 617

    In Management Studio try to open the custom report attached

    it will show you sqljobs time line for a whole day grouped by 30 minutes chunks (intervals)

    if you click on an interval you will see details (per minute information)

     

    The pic below is not a good example (my server is Express edition), but nonetheless...

    sqljobs_timeline_example

    Attachments:
    You must be logged in to view attached files.
  • Feivel

    SSCertifiable

    Points: 7486

    Grant Fritchey wrote:

    The information is captured in system tables. You just have to query those and you're off and running. Take a look at sys.jobhistory. Then, to only get the longest one, SELECT TOP 1 .... ORDER BY Duration. Ta da

    I hope it could be that simple. Do you have a working example on how to logically put this through. I queried that sys.jobhistory table but I don't want to get my duration based on steps. I would like to have the final job result and order it by duration.

    • This reply was modified 3 months, 1 week ago by  Feivel.
  • Sue_H

    SSC Guru

    Points: 90011

    You can try playing with something like the following to grab jobs that were successful and just look at the duration of the job (instead of each job step):

    SELECT 
    j.name,
    run_date,
    max(h.run_duration) RunDuration
    FROM dbo.sysjobhistory h
    INNER JOIN dbo.sysjobs j
    ON h.job_id = j.job_id
    WHERE step_name like '(Job outcome)%'
    AND run_status = 1
    GROUP BY j.name, run_date
    ORDER BY RunDuration desc

     

    Sue

  • ScottPletcher

    SSC Guru

    Points: 98053

    Here's a sample query that finds jobs that run a certain % of time over previous runs.  Naturally you can tweak the percentages and prior run counts as you prefer to:

    IF OBJECT_ID('tempdb.dbo.#job_duration_factors') IS NOT NULL
    DROP TABLE #job_duration_factors
    CREATE TABLE #job_duration_factors (
    run_duration_mins_min int NOT NULL,
    run_duration_mins_max int NOT NULL,
    required_percentage_over decimal(5, 2) NOT NULL
    )
    INSERT INTO #job_duration_factors VALUES
    ( 0, 5, 100.00),
    ( 6, 10, 80.00),
    (11, 15, 60.00),
    (16, 20, 50.00),
    (21, 30, 33.33),
    (31, 45, 25.00),
    (46, 60, 15.00),
    (61,999999, 10.00)


    SELECT j.name,
    MAX(CASE WHEN jh.row_num = 1 THEN jh.run_duration_mins ELSE 0 END) AS last_run_time_mins,
    SUM(CASE WHEN jh.row_num > 1 THEN 1 ELSE 0 END) AS prior_run_count,
    MAX(CASE WHEN jh.row_num > 1 THEN jh.run_duration_mins ELSE 0 END) AS max_mins_prior_runs
    FROM (
    SELECT
    run_duration / 10000 * 60 + run_duration % 10000 / 100 AS run_duration_mins,
    ROW_NUMBER() OVER(PARTITION BY job_id ORDER BY instance_id DESC) AS row_num,
    *
    FROM msdb.dbo.sysjobhistory
    WHERE step_id = 0 AND run_status = 1
    ) AS jh
    INNER JOIN msdb.dbo.sysjobs j ON j.job_id = jh.job_id
    INNER JOIN #job_duration_factors jdf ON
    jh.run_duration_mins BETWEEN jdf.run_duration_mins_min AND jdf.run_duration_mins_max
    WHERE jh.row_num BETWEEN 1 AND 4
    GROUP BY j.name
    HAVING
    (MAX(CASE WHEN jh.row_num = 1 THEN jh.run_duration_mins ELSE 0 END) -
    MAX(CASE WHEN jh.row_num > 1 THEN jh.run_duration_mins ELSE 0 END)) * 100.0 /
    NULLIF(MAX(CASE WHEN jh.row_num > 1 THEN jh.run_duration_mins ELSE 0 END), 0) >
    MAX(jdf.required_percentage_over)

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • This was removed by the editor as SPAM

  • Feivel

    SSCertifiable

    Points: 7486

    Sue_H wrote:

    You can try playing with something like the following to grab jobs that were successful and just look at the duration of the job (instead of each job step):

    SELECT 
    j.name,
    run_date,
    max(h.run_duration) RunDuration
    FROM dbo.sysjobhistory h
    INNER JOIN dbo.sysjobs j
    ON h.job_id = j.job_id
    WHERE step_name like '(Job outcome)%'
    AND run_status = 1
    GROUP BY j.name, run_date
    ORDER BY RunDuration desc

    Sue

    Thanks! Sue. However, I am looking for something wherein I can check for the run duration for the past 24 hrs. and only dump the jobs in the table that are within that time window. Your code gives me a lot more than I actually need to consume. Do you have something of that nature.

  • Sue_H

    SSC Guru

    Points: 90011

    You would just filter on run_date to work in your scenario or possibly include run_time if you are doing last 24 hours.

    Sue

  • Jeff Moden

    SSC Guru

    Points: 994284

    Feivel wrote:

    Sue_H wrote:

    You can try playing with something like the following to grab jobs that were successful and just look at the duration of the job (instead of each job step):

    SELECT 
    j.name,
    run_date,
    max(h.run_duration) RunDuration
    FROM dbo.sysjobhistory h
    INNER JOIN dbo.sysjobs j
    ON h.job_id = j.job_id
    WHERE step_name like '(Job outcome)%'
    AND run_status = 1
    GROUP BY j.name, run_date
    ORDER BY RunDuration desc

    Sue

    Thanks! Sue. However, I am looking for something wherein I can check for the run duration for the past 24 hrs. and only dump the jobs in the table that are within that time window. Your code gives me a lot more than I actually need to consume. Do you have something of that nature.

    MSDB has a "dbo.agent_datetime" function built in.  If you pass it the RunDate column and the RunTime column, it will return a DATETIME that you could compare the run date and time with in the WHERE clause.  It's not normally a good practice to do such a thing but the design of the MSDB tables don't allow for much else.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply