T-SQL statement to return SQL Job Last Run Date

• Golova

  Mr or Mrs. 500

  Points: 578

  More actions

  February 1, 2013 at 4:53 pm

  #1583612

  Do you have the script please to use sysjobactivity?

  I would like to bring all the jobs with their names, steps, and the status, last run time, even if there is no history, or the jobs is disabled, not scheduled

• Darkness_GR

  SSC Rookie

  Points: 31

  More actions

  June 18, 2014 at 9:28 pm

  #1723062

  Bringing a thread back from the dead but to help people that stumble accross this page in the future

  you could use the sql system function msdb.dbo.agent_datetime(run_date, run_time) to calculate the date time value from the run_date and run_time integer values stored in the msdb.dbo.sysjobhistory table.

  e.g.

  Select J.name,MAX(msdb.dbo.agent_datetime(run_date, run_time)) as 'LastLogRun'

  FROM msdb.dbo.sysjobhistory H

  Inner join msdb.dbo.sysjobs J ON H.job_id = J.job_id

  where step_id = 0

  GROUP BY J.name

• dbonamie

  Newbie

  Points: 9

  More actions

  June 27, 2018 at 2:26 pm

  #1995523

  This returns runtime information:
  SELECT
      CAST(STR(h.run_date, 8, 0) AS DATE) AS StartDate
  ,   SUBSTRING(CAST(CAST(CAST(STR(h.run_date, 8, 0) AS DATETIME)
                + CAST(STUFF(STUFF(RIGHT('000000'
                                         + CAST (h.run_time AS VARCHAR(6)), 6),
                                   5, 0, ':'), 3, 0, ':') AS DATETIME) AS TIME) AS VARCHAR(16)),
                1, 8) AS StartTime
  ,   STUFF(STUFF(REPLACE(STR(run_duration, 6, 0), ' ', '0'), 3, 0, ':'), 6, 0,
            ':') AS Run_Duration
  ,   j.name JobName
  ,   h.step_id StepNumber
  ,   h.step_name StepName
  ,   CAST(STR(h.run_date, 8, 0) AS DATETIME)
      + CAST(STUFF(STUFF(RIGHT('000000' + CAST (h.run_time AS VARCHAR(6)), 6), 5,
                         0, ':'), 3, 0, ':') AS DATETIME) AS StartDatetime
  ,   DATEADD(SECOND,
              ( ( h.run_duration / 1000000 ) * 86400 ) + ( ( ( h.run_duration
                                                               - ( ( h.run_duration
                                                                / 1000000 )
                                                                * 1000000 ) )
                                                             / 10000 ) * 3600 )
              + ( ( ( h.run_duration - ( ( h.run_duration / 10000 ) * 10000 ) )
                    / 100 ) * 60 ) + ( h.run_duration - ( h.run_duration / 100 )
                                       * 100 ),
              CAST(STR(h.run_date, 8, 0) AS DATETIME)
              + CAST(STUFF(STUFF(RIGHT('000000'
                                       + CAST (h.run_time AS VARCHAR(6)), 6), 5,
                                 0, ':'), 3, 0, ':') AS DATETIME)) AS EndDatetime
  ,   CASE h.run_status
        WHEN 0 THEN 'failed'
        WHEN 1 THEN 'Succeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
        WHEN 4 THEN 'In Progress'
      END AS ExecutionStatus
  ,   h.message MessageGenerated
  FROM
      msdb.dbo.sysjobhistory h
  INNER JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
  WHERE
      h.step_id > 0 -- eliminates job total information row
  ORDER BY
      StartDate ASC
  ,   StartTime ASC;