Last Successful Completion Date for SQL Job

• AMCiti

  SSC-Addicted

  Points: 496

  More actions

  October 15, 2021 at 11:36 am

  Go to Answer

  #3940861

  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

• Jeff Moden

  SSC Guru

  Points: 1004340

  More actions

  October 15, 2021 at 2:26 pm

  Answer

  #3940916

  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)