SQL Job History Information

• SQL_Kills

  Hall of Fame

  Points: 3154

  More actions

  April 29, 2014 at 10:21 am

  #285011

  Hi,

  I want to query the Job within the Job Activity Monitor, I would like to get the following information:

  Job Name,

  Setp No,

  Step Name,

  Start Time of when Job last ran,

  End Time of when Job last ran,

  Duration (formatted in HH:MM:SS)

  Thanks

• Stuart B Smith

  Newbie

  Points: 3

  More actions

  April 29, 2014 at 12:10 pm

  #1709587

  This should give you what you are looking for. Added status column also.

  SELECT

  jobs.name AS 'Job Name'

  ,step_id AS 'Step No'

  ,step_name AS 'Step Name'

  ,StartTime AS 'Start Time'

  ,DATEADD(SECOND,

  DATEPART(HOUR,Duration) * 3600 +

  DATEPART(MINUTE,Duration) * 60 +

  DATEPART(SECOND,Duration),StartTime)

  AS 'End Time'

  ,Duration

  ,CASE run_status

  WHEN 0 THEN 'Failed'

  WHEN 1 THEN 'Succeeded'

  WHEN 2 THEN 'Retry'

  WHEN 3 THEN 'Cancelled'

  END AS Status

  FROM msdb.dbo.sysjobs jobs

  INNER JOIN (

  SELECT

  job_id

  ,step_id

  ,step_name

  ,run_date

  ,msdb.dbo.agent_datetime(run_date, run_time) as 'StartTime'

  ,CAST(

  SUBSTRING(RIGHT('000000' + CAST(run_duration AS VARCHAR(6)),6),1,2) + ':' +

  SUBSTRING(RIGHT('000000' + CAST(run_duration AS VARCHAR(6)),6),3,2) + ':' +

  SUBSTRING(RIGHT('000000' + CAST(run_duration AS VARCHAR(6)),6),5,2)

  AS TIME) AS Duration

  ,run_status

  FROM msdb.dbo.sysjobhistory

  ) AS hist ON jobs.job_id = hist.job_id

  ORDER BY StartTime, jobs.job_id, step_id