{"id":585,"date":"2016-01-20T12:54:09","date_gmt":"2016-01-20T18:54:09","guid":{"rendered":"https:\/\/jackdonnell.com\/?p=585"},"modified":"2016-01-20T16:57:07","modified_gmt":"2016-01-20T22:57:07","slug":"sql-agent-jobs-last-run","status":"publish","type":"post","link":"https:\/\/jackdonnell.com\/?p=585","title":{"rendered":"SQL Agent Jobs Last Run"},"content":{"rendered":"<p>Sometimes there is a need to see last run time information for a SQL Agent job.\u00a0You may need to see when the job is scheduled to run next.\u00a0 The <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms187112.aspx\" target=\"_blank\">msdb<\/a> system database contains a ton of information about SQL Agent jobs schedules, activity, job steps and etc..<\/p>\n<p>I created the below query to get a bird&#8217;s eye view of job executions. The query is based off <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms189817.aspx\" target=\"_blank\">msdb.dbo.sysjobs<\/a> and <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms190484.aspx\" target=\"_blank\">msdb.dbo.sysjobactivity<\/a>. These objects have a lot more information that can be added. Tweak the results to\u00a0meet your needs.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-631\" src=\"https:\/\/jackdonnell.com\/wp-content\/uploads\/2016\/01\/Jobrollup3.gif\" alt=\"Jobrollup3\" width=\"849\" height=\"271\" \/><\/p>\n<p><!--more--><\/p>\n<p>The query below determines if a job is enabled, when it is scheduled to run( or not), last start time (or not), last time it completed and duration in minutes.The results have been limited to job scheduled to run today or in the future and a specific job named &#8216;Your Job Name Goes Here&#8217;. There is not a dependency for either of these items in the where clause.<\/p>\n<pre>SET NOCOUNT ON \r\nUSE msdb\r\nGO\r\n-- Find Jobs next run date with last and time\r\n-- jackdonnell.com\r\n\r\nSELECT\r\n j.name\r\n ,case when j.enabled = 1 then 'yes' else 'no' end [enabled]\r\n ,ja.[next_scheduled_run_date]\r\n ,ja.[start_execution_date]\r\n ,ja.[stop_execution_date]\r\n ,datepart(minute,(ja.[stop_execution_date] - ja.[start_execution_date])) as [last_run_minutes]\r\nFROM msdb.dbo.sysjobs j \r\nINNER JOIN [msdb].[dbo].[sysjobactivity] ja \r\n ON ja.[job_id] = j.job_id \r\nWHERE ja.[next_scheduled_run_date] &gt; CONVERT(VARCHAR(10), getdate(), 121)\r\nAND j.[name] = 'Your Job Name Goes Here' -- Filter to specific job name\r\nORDER BY j.[name],ja.[next_scheduled_run_date]\r\nGO<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Sometimes there is a need to see last run time information for a SQL Agent job.\u00a0You may need to see when the job is scheduled to run next.\u00a0 The msdb system database contains a ton of information about SQL Agent &hellip;<\/p>\n<p class=\"read-more\"><a href=\"https:\/\/jackdonnell.com\/?p=585\">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":[266,22,5],"tags":[395,303,394,393,403,404,315,249,35,288,52,291],"class_list":["post-585","post","type-post","status-publish","format-standard","hentry","category-dba","category-sql-server","category-t-sql","tag-agent-jobs","tag-dba","tag-dbo-sysjobactivity","tag-dbo-sysjobs","tag-inner-join","tag-job_id","tag-query","tag-script","tag-select","tag-sql-agent","tag-ssms","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/585","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=585"}],"version-history":[{"count":8,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/585\/revisions"}],"predecessor-version":[{"id":636,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/585\/revisions\/636"}],"wp:attachment":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=585"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=585"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=585"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}