{"id":313,"date":"2015-12-27T22:30:12","date_gmt":"2015-12-28T04:30:12","guid":{"rendered":"https:\/\/jackdonnell.com\/?p=313"},"modified":"2015-12-27T23:04:09","modified_gmt":"2015-12-28T05:04:09","slug":"script-to-get-sql-agent-job-history","status":"publish","type":"post","link":"https:\/\/jackdonnell.com\/?p=313","title":{"rendered":"Script to Get SQL Agent Job History"},"content":{"rendered":"<p>Wanted to share this script. I pieced it together some time ago from various scripts. I think this might be the <a href=\"http:\/\/www.sqlteam.com\/forums\/topic.asp?TOPIC_ID=115785\">source<\/a> for at least some of the query.<\/p>\n<p>Removing the step_ID from the where clause will show run times by step_id and Total RunTime (step_id 0).<br \/>\n<!--more--><\/p>\n<pre lang=\"tsql\" line=\"1\" escaped=\"true\">\r\nUSE master\r\nGO\r\nSELECT SERVERPROPERTY('ServerName') [ServerName\/Instance]\r\n,job_name\r\n,CASE WHEN step_id = 0 then 'Total Run Time' else CAST(step_id as varchar(10))End as step_id\r\n,step_name\r\n,datename(DW,run_datetime)StartDate\r\n,run_datetime\r\n,run_datetime + CASE WHEN run_duration = '00:00:0*' then '00:00:00'\r\nELSE run_duration END [run_end_time]\r\n,CASE WHEN run_duration = '00:00:0*' then '00:00:00'\r\nELSE run_duration END run_duration\r\n,Run_status\r\nFROM\r\n(\r\nSELECT job_name,step_id,step_name, run_datetime,\r\nSUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +\r\nSUBSTRING(run_duration, 5, 2) AS run_duration\r\n,CASE WHEN run_status = 0 then 'Failed'\r\nWHEN run_status =1 then 'Succeeded'\r\nWHEN run_status =2 then 'Retry'\r\nWHEN run_status =3 then 'Canceled'\r\nWHEN run_status =4 then 'In progress'\r\nELSE 'Unknown'\r\nEND run_status\r\n,[Results]\r\nFROM\r\n(\r\nSELECT DISTINCT\r\nj.name as job_name, step_name,step_id,run_date as rd,\r\nrun_datetime = CONVERT(DATETIME, RTRIM(run_date)) +\r\n(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) \/ 216e4,\r\nrun_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)\r\n,run_status\r\n,h.message [Results]\r\nFROM msdb..sysjobhistory h\r\ninner join msdb..sysjobs j\r\non h.job_id = j.job_id\r\n) t\r\n) t\r\nWHERE\r\n--step_id &lt;&gt; '0' AND -- Show step completion times\r\nstep_id = 0 AND -- Show only the run times\r\n--(step_id = 3 or step_id =2) AND -- Show specific step\r\nrun_datetime &gt;CONVERT(varchar(10),GetDate()-1,121) ---Yesterday &amp; Today\r\nORDER BY run_datetime, job_name, step_id <\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Wanted to share this script. I pieced it together some time ago from various scripts. I think this might be the source for at least some of the query. Removing the step_ID from the where clause will show run times &hellip;<\/p>\n<p class=\"read-more\"><a href=\"https:\/\/jackdonnell.com\/?p=313\">Read more &raquo;<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[273,266,22,5],"tags":[213,289,303,315,249,35,36,288,295,52,291],"class_list":["post-313","post","type-post","status-publish","format-standard","hentry","category-administration","category-dba","category-sql-server","category-t-sql","tag-213","tag-2008-r2","tag-dba","tag-query","tag-script","tag-select","tag-sql","tag-sql-agent","tag-sql-server","tag-ssms","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/313","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=313"}],"version-history":[{"count":11,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/313\/revisions"}],"predecessor-version":[{"id":515,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/313\/revisions\/515"}],"wp:attachment":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=313"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=313"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=313"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}