Query SQL Server Job Tables

If you have ever wanted to query the SQL Server job tables then you know they are a little cryptic. The following query will assist you in getting some useful information out of those tables. I recently wanted to be able to query these tables and get the error for when there was a failure during the running of the job. This can be accomplished using this query and just adding another couple of fields. You can add the job history (jh alias in this query) sql_severity and message columns. Then you can simply do a where statement to search the message column for a particular error or for particular keywords that pertain to errors, such as something novel like “error”!! Hope this helps. You can also get some useful information from the table msdb..jobactivity.

CONVERT(DATETIME, RTRIM(jh.run_date)) + (jh.run_time * 9 + jh.run_time % 10000 * 6 + jh.run_time % 100 * 10) / 216e4 AS RunDateTime,
j.name AS JobName,
jh.step_id AS StepID,
SUBSTRING(RIGHT(‘000000’ + CONVERT(varchar(6), jh.run_duration), 6), 1, 2) + ‘:’ + SUBSTRING(RIGHT(‘000000’ + CONVERT(varchar(6), jh.run_duration), 6), 3, 2) + ‘:’ + SUBSTRING(RIGHT(‘000000’ + CONVERT(varchar(6), jh.run_duration), 6), 5, 2) AS StepRunTime,
jh.step_name AS StepName
FROM msdb..sysjobhistory jh
INNER JOIN msdb..sysjobs j ON jh.job_id = j.job_id
ORDER BY jh.run_date, jh.run_time, j.job_id, jh.step_id

Bradley Schacht

Bradley Schacht is a Cloud Solution Architect on the state and local government team with Microsoft based in Jacksonville, FL. He has co-authored 3 SQL Server books including "SQL Server 2014 Professional Administration". As a former consultant and trainer, he uses his experience on many parts of the Microsoft BI and data platform to help customers deliver the best possible solutions. Bradley frequently presents at community events around the country. He is a contributor to sites such as SQLServerCentral.com and an active member of the Jacksonville SQL Server User Group (JSSUG).

You may also like...

1 Response

  1. JamesB says:

    This code snippet is quite handy and will save me a lot of time whenever I have to research job activity. Thanks!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.