Last Successful Completion Date for SQL Job

  • I am trying to write a query to get a list of all SQL Jobs that have failed.  The part I am having an issue with is getting the "Last Successful Date" the SQL Job completed without errors.  It seems what I am getting is the last "successful step", not the last time the entire SQL Job completed without errors.  I am hoping someone can help show my error.  Below is my current code, but the "As Last_Successful_Date" subquery is the part I am having an issue with at the moment.

    SELECT DISTINCT 
    J.NAME AS "Job_Name",
    J.DESCRIPTION AS "Job_Description",
    H.Step_Name AS "Step_Name",
    H.MESSAGE AS "Error_Message",
    MSDB.dbo.Agent_Datetime(h.run_date, h.run_time) AS Last_Status_Date,
    (SELECT MAX(MSDB.dbo.Agent_Datetime(run_date, run_time)) FROM MSDB.dbo.SysJobHistory WHERE run_status = 1 AND job_id = j.job_id) AS Last_Successful_Date
    FROM
    MSDB.dbo.SysJobHistory H,
    MSDB.dbo.SysJobs J
    WHERE
    J.job_id = H.job_id
    AND h.run_date = (SELECT MAX(hi.run_date) FROM MSDB.dbo.SysJobHistory hi WHERE h.job_id = hi.job_id)
    AND ((h.run_status = 0) OR (h.run_status = 3))
    AND h.step_name <> '(Job outcome)'
    AND J.NAME NOT LIKE 'DBAMaint_%'
    ORDER BY
    J.NAME
  • If you filter on step_id = 0, you'll get the info for the entire job instead of the final step.

     

    --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.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

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

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