SQL SERVER – T-SQL Script to Check SQL Server Job History

5 Comments. Leave new

• 

  Dominic Wirth

  January 4, 2019 2:47 pm

  Here is a script that I created and which goes in the same direction. It shows and overview of all jobs and gives some statistic informations.

  WITH JobCounters AS
  (
  SELECT
  job_id
  ,MIN(run_duration) AS MinRunDurationSeconds
  ,MAX(run_duration) AS MaxRunDurationSeconds
  ,MAX([msdb].[dbo].[agent_datetime]([run_date], [run_time])) AS LastStartDateTime
  ,AVG(run_duration) AS AvgRunDurationSeconds
  FROM [msdb].[dbo].[sysjobhistory]
  WHERE step_id = 0 — Job Outcome
  GROUP BY job_id
  )
  SELECT
  J.[name] AS JobName
  ,JC.[name] AS JobCategory
  ,CAST((CASE WHEN J.[enabled] = 1 THEN 1 ELSE 0 END) AS BIT) AS IsEnabled
  ,(CASE
  WHEN LastJobExecution.run_status = 2 THEN ‘RETRY_STEP’
  WHEN JA.job_id IS NOT NULL AND JA.stop_execution_date IS NULL AND JA.start_execution_date IS NOT NULL THEN ‘EXECUTING…’
  WHEN LastJobExecution.run_status = 0 THEN ‘ERROR’
  WHEN LastJobExecution.run_status = 1 THEN ‘SUCCESS’
  WHEN LastJobExecution.run_status = 3 THEN ‘ABORT’
  ELSE NULL
  END) AS LastRunStatus
  ,COALESCE(JA.start_execution_date, JobCounters.LastStartDateTime) AS LastStartDateTime
  ,JA.next_scheduled_run_date AS NextStartDateTime
  ,(CASE JA.run_requested_source WHEN 1 THEN ‘SQL Agent’ WHEN 2 THEN ‘Alarming’ WHEN 3 THEN ‘Boot’ WHEN 4 THEN ‘User’ ELSE JA.run_requested_source END) AS StartedBy
  ,JS.step_name AS LastJobStep
  ,LastJobExecution.run_duration AS LastRunDurationSeconds
  ,JobCounters.MinRunDurationSeconds
  ,JobCounters.MaxRunDurationSeconds
  ,JobCounters.AvgRunDurationSeconds
  FROM [msdb].[dbo].[sysjobs] AS J
  LEFT JOIN [msdb].[dbo].[syscategories] AS JC ON J.category_id = JC.category_id
  LEFT JOIN JobCounters ON J.job_id = JobCounters.job_id
  LEFT JOIN [msdb].[dbo].[sysjobactivity] JA ON J.job_id = JA.job_id
  LEFT JOIN [msdb].[dbo].[sysjobsteps] JS ON JA.job_id = JS.job_id AND JA.last_executed_step_id = JS.step_id
  LEFT JOIN (
  SELECT run_duration, run_status, step_id, job_id, run_date, run_time, retries_attempted, [message], ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY instance_id DESC) AS RowId
  FROM msdb.dbo.[sysjobhistory]
  ) AS LastJobExecution ON J.job_id = LastJobExecution.job_id AND ISNULL(LastJobExecution.RowId, 1) = 1
  WHERE
  JA.session_id IS NULL
  OR EXISTS (SELECT NULL FROM [msdb].[dbo].[sysjobactivity] GROUP BY job_id HAVING job_id = J.job_id AND MAX(session_id) = JA.session_id)
  ORDER BY J.[name];

  Reply
• 

  charan

  January 18, 2019 12:24 am

  Thanks so much for sharing such valuable script

  Reply
• 

  Ashley Hoff

  July 23, 2019 5:23 am

  OK, from my experience, the run duration is not in seconds. The run duration is a representation of the time run, with the specific separators removed. For example, I have a job that runs for just over an hour. The run duration is represented as 10212 via the script above.

  This value represents 01:02:12, being 1 hour, 2 minutes and 12 seconds.

  Please keep this in mind when using this script.

  Reply
• 

  skcwebworld

  September 3, 2022 2:58 am

  Hi all,
  I need a small help in order to improve my knowledge. I have archive the SSIS job history of all SSIS jobs rather than purging it. At the time of requirement, we can restore the history from Archived table. In that case do we need to Archive the data of only table [msdb].[dbo].[sysjobhistory], OR any other table I have to include in Archival Activity? If someone can confirm, that will be helpful.
  Thanks in advance.

  Reply