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.
SELECTCONVERT(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 StepNameFROM msdb..sysjobhistory jhINNER JOIN msdb..sysjobs j ON jh.job_id = j.job_idORDER BY jh.run_date, jh.run_time, j.job_id, jh.step_id
Good stuff, Brad! But one question...
Was there a reason to use the GROUP BY? I don't see a need for it, or is it a leftover from querying other aspects of the job history table?... It seems the GROUP BY gave you the results in order, but wouldn't ORDER BY be more appropriate?
Sorry if it sounds like I am nitpicking...
JEBacaniSQLDude You are correct, this particular query probably doesn't need the GROUP BY. However, there have been times when I have gotten duplicate steps listed in the results. This may very well could have been from the other columns that I had in there as I was joining to other tables too. At the time I was using this method for pulling the errors from different jobs. An order by would definitely be appropriate here, be in instead of or in addition to. I looked at the script that I have saved and that is in there but for whatever reason while I was editing stuff for this blog removed it. Great catch!
You're definitely NOT nitpicking. Very valid questions. I have updated the script given here to add the ORDER BY and remove the Group By for this example.
Thanks for the feedback!