SQL agent failed job report last 24 hours

  • Hi There,

    I need some script to take a report of failed agent job, only failed if it is success in next run ignore it.

    I can able to get failed job, but if it success in next run it is generating that as well.

  • Use something like a NOT EXISTS clause to filter out anything that has had a subsequent success

    Pseudo code

    SELECT
    job_name
    FROM
    msdb.dbo.sysjobhistory
    WHERE
    date >= convert(date,getdate())
    AND
    date < convert(date,getdate()+1)
    AND
    status = 'failed'
    AND NOT EXISTS
    (SELECT
    job_name
    FROM
    msdb.dbo.sysjobhistory
    WHERE
    date >= convert(date,getdate())
    AND
    date < convert(date,getdate()+1)
    AND
    status = 'success'
    )
  • Thanks, that does not working.

  • for the query below, the h.[run_status]  is what determines what was successful/failed/inprogress.

    add the filter to limit it to failed only, and change the date range.

     

    --desc: gets job history, and when the GUI times out
    DECLARE @Job_name varchar(128) = ''; --optional search for a specific job
    SELECT TOP 100
    CASE h.[run_status]
    WHEN 0 THEN 'Failed' WHEN 1 THEN 'Success'
    WHEN 3 THEN 'Cancelled'
    WHEN 4 THEN 'InProgress'
    ELSE 'Retry'
    END AS JobStatus,
    j.name as 'JobName',
    s.step_id as 'Step',
    s.step_name as 'StepName',
    msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
    ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60)
    as 'RunDurationMinutes',
    h.[message]
    From msdb.dbo.sysjobs j
    INNER JOIN msdb.dbo.sysjobsteps s
    ON j.job_id = s.job_id
    INNER JOIN msdb.dbo.sysjobhistory h
    ON s.job_id = h.job_id
    AND s.step_id = h.step_id
    AND h.step_id <> 0
    where 1=1
    AND j.enabled = 1 --Only Enabled Jobs
    and (j.name = @Job_name OR @Job_name = '' )

    and msdb.dbo.agent_datetime(run_date, run_time)
    BETWEEN '2021-05-01' and '2021-06-01' --Uncomment for date range queries

    order by JobName, RunDateTime desc

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thank you Lowell. I got similar kind of code from some website. But, I do not know, how to remove next run success job from the result.

    Ex: job_A runs every 5 minutes - It failed at 11 AM and success at 11:05, which means it should not be appear in the result.

    Ex: job_B runs every 5 minutes - It failed at 11 AM, success at 11:05, failed 11:10 which means it should be appear in the result.

    When I run the query to collect the report, if the job run is success in the last run, even if it failed earlier runs can be ignored. If the last run is failed add that into a report. if it has multiple entries, just add only one entries  (Tried with row_number).

    I tried some CTE, not working as expected.

    • This reply was modified 7 months, 3 weeks ago by  sqltester1.
  • ok try this:

    this returns only jobs whos last status was failed.

     

    ignore the extra variables in there, it is an artifact of a process I run that gets the fully qualified server name(is MyRealServer.mydomain.com,1433 and also what layer it might be (ie Dev, UAT, Prod, whatever)

    -- Failed Jobs executed since the Last run hours, where the last status is failed. 
    --This skips jobs that failed once,and subsequently executed successfully on the following run(s).

    DECLARE @Layer VARCHAR(30); SET @Layer = '[CMSServerLayer]'
    DECLARE @CMSFQServerName VARCHAR(128); SET @CMSFQServerName = '[CMSFQServerName]'
    DECLARE @dt CHAR(8);
    SET @dt= CASE
    WHEN DATENAME(dw,GETDATE()) = 'Monday'
    THEN CONVERT(CHAR(8), (SELECT DATEADD (DAY,(-3), GETDATE())), 112)
    ELSE CONVERT(CHAR(8), (SELECT DATEADD (DAY,(-1), GETDATE())), 112)
    END
    SELECT
    @CMSFQServerName AS CMSFQServerName,
    @Layer AS ServerLayer,
    CONVERT(varchar(128),@@SERVERNAME) As Servername,
    T1.step_name AS [Step Name],
    SUBSTRING(T2.name,1,140) AS [SQL Job Name],
    --msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
    COUNT(*) AS TotalFailures,
    CAST(MIN(CONVERT(DATETIME,CAST(run_date AS CHAR(8)),101)) AS CHAR(11)) AS [MinFailure Date],
    CAST(MAX(CONVERT(DATETIME,CAST(run_date AS CHAR(8)),101)) AS CHAR(11)) AS [MaxFailure Date],
    MAX(msdb.dbo.agent_datetime(T1.run_date, T1.run_time)) AS 'RunDateTime',
    MIN(T1.run_duration) StepDuration,
    CASE MIN(T1.run_status)
    WHEN 0 THEN 'Failed'
    WHEN 1 THEN 'Succeeded'
    WHEN 2 THEN 'Retry'
    WHEN 3 THEN 'Cancelled'
    WHEN 4 THEN 'In Progress'
    END AS ExecutionStatus,
    MAX(T1.message) AS [Error Message]
    FROM
    msdb..sysjobhistory T1 INNER JOIN msdb..sysjobs T2 ON T1.job_id = T2.job_id
    WHERE
    T1.run_status NOT IN (1,2,4)
    AND T1.step_id != 0
    AND run_date >= @dt
    GROUP BY
    T1.step_name,
    T2.name


    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thank you Lowell. It is working good.

    I just have few doubts.

    Is there any difference to use this, both are returning same date and format.

    "SET @dt=CONVERT(CHAR(8),DATEADD(HH,-24,GETDATE()),112) " . Since yours showing some extra rows than this.

    DECLARE @dt CHAR(8);
    SET @dt= CASE
    WHEN DATENAME(dw,GETDATE()) = 'Monday'
    THEN CONVERT(CHAR(8), (SELECT DATEADD (DAY,(-3), GETDATE())), 112)
    ELSE CONVERT(CHAR(8), (SELECT DATEADD (DAY,(-1), GETDATE())), 112)
    END

    SET @dt=CONVERT(CHAR(8),DATEADD(HH,-24,GETDATE()),112)
  • it might not be obvious, but the query is assuming a monday thru friday work week.

    so if today is monday, i want errors from Sat + Sun + so far today, otherwise, just the errors in the last 24 hours.

    it's basically the same code i use as part of a daily report each day, to give me an idea of issues.

     

    if you change it to just last 24 hours like you are suggesting, i would assume you check the report each day.

     

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thank you for your help and response. Happy learning.

    Have great weekend.

  • Hi Lowell,

    I run into another problem, I when run this code to get the report almost 10+servers, I could get lot of failed jobs ex:100+ jobs.

    When I go and manually check the next run it automatically got successful. Is there any way to exclude those, if the multiple times failed job  is successful in the last run those can be exclude, so that it is easy work for me to go and check job manually only the failed job that never successful till my report run.

  • SELECT j.name AS job_name, jh.*
    FROM (
    SELECT jh.job_id, MAX(jh.instance_id) AS instance_id
    FROM msdb.dbo.sysjobhistory jh
    WHERE jh.run_status = 0 AND
    /* limit to last 7 days only -- adjust this as you need to */
    jh.run_date >= CONVERT(varchar(8), DATEADD(DAY, -7, GETDATE()), 112)
    GROUP BY jh.job_id
    ) AS failed_jobs
    INNER JOIN msdb.dbo.sysjobhistory jh ON jh.instance_id = failed_jobs.instance_id
    INNER JOIN msdb.dbo.sysjobs j ON j.job_id = jh.job_id
    WHERE
    NOT EXISTS(
    SELECT 1
    FROM msdb.dbo.sysjobhistory jh
    WHERE
    jh.instance_id > failed_jobs.instance_id AND
    jh.job_id = failed_jobs.job_id AND
    jh.step_id <= jh.step_id
    )

    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."

  • Hi ScottPletcher,

    It is not working.

    This is working same as Lowell script, but the whole script returns zero rows.

    I have checked some job is failed in the last run, that is not reporting from the query you have shared. I think some issue with if exists.

    SELECT jh.job_id, MAX(jh.instance_id) AS instance_id

    FROM msdb.dbo.sysjobhistory jh

    WHERE jh.run_status = 0 AND

    jh.run_date >= CONVERT(varchar(8), DATEADD(DAY, -1, GETDATE()), 112)

    GROUP BY jh.job_id

    • This reply was modified 7 months, 2 weeks ago by  sqltester1.

Viewing 12 posts - 1 through 11 (of 11 total)

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