{"id":59,"date":"2014-01-23T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/sql-agent-monitoring-long-running-job-queries"},"modified":"2026-03-18T21:50:50","modified_gmt":"2026-03-18T21:50:50","slug":"sql-agent-monitoring-long-running-job-queries","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2014\/01\/23\/sql-agent-monitoring-long-running-job-queries\/","title":{"rendered":"SQL Agent Monitoring – Long Running Job Queries"},"content":{"rendered":"

The SQL Server alert system does a great job of handling notifications for SQL Agent job failures but if want to know if a job is hung, taking longer than expected, or maybe it\u2019s disabled and hasn\u2019t been running, we will need to put in a bit of effort ourselves.<\/p>\n

This first query will return the start and stop times of the last execution of a job. If the job is currently running then it will provide the duration in seconds that it has been running for.\u00a0This can easily be put into a SQL Agent job to run every few minutes and send out an e-mail with jobs that have been running for longer than you\u2019d like.<\/p>\n

SELECT jobs.name AS [Job_Name]\n , CONVERT(VARCHAR(23),ja.start_execution_date,121)\n AS [Start_execution_date]\n , ISNULL(CONVERT(VARCHAR(23),ja.stop_execution_date,121), 'Is Running')\n AS [Stop_execution_date]\n , CASE ISNULL(CAST(ja.stop_execution_date AS VARCHAR(30)),'NULL')\n WHEN 'NULL'\n THEN CAST(DATEDIFF(ss,ja.start_execution_date,GETDATE()) AS VARCHAR(30))\n ELSE 'Not running'\n END AS [Duration_secs]\nFROM msdb.dbo.sysjobs jobs\nLEFT JOIN msdb.dbo.sysjobactivity ja ON ja.job_id = jobs.job_id\n AND ja.start_execution_date IS NOT NULL\nWHERE jobs.name = 'test'<\/code><\/pre>\n
\"\"<\/a><\/p>\n
\"\"<\/a><\/p>\n
This next query will return the last execution and status for a given job, or all jobs if you comment out the WHERE clause. This can be used to monitor how long it\u2019s been since a job last succeeded.<\/p>\n
;WITH last_hist_rec AS\n\n
(\n SELECT ROW_NUMBER() OVER\n (PARTITION BY job_id ORDER BY run_date DESC, run_time DESC) AS [RowNum]\n , job_id\n , run_date AS [last_run_date]\n , run_time AS [last_run_time]\n , CASE run_status\n WHEN 0 THEN 'Failed'\n WHEN 1 THEN 'Succeeded'\n WHEN 2 THEN 'Retry'\n WHEN 3 THEN 'Canceled'\n END AS [run_status_desc]\n FROM msdb.dbo.sysjobhistory\n )\nSELECT jobs.name AS [job_name]\n , hist.last_run_date\n , hist.last_run_time\n , hist.run_status_desc\nFROM msdb.dbo.sysjobs jobs\nLEFT JOIN last_hist_rec hist ON hist.job_id = jobs.job_id\n AND hist.RowNum = 1\nWHERE jobs.name = 'test'\n --AND hist.[run_status_desc] = 'Failed'\n --AND hist.[run_status_desc] = 'Succeeded'\n --AND hist.[run_status_desc] = 'Retry'\n --AND hist.[run_status_desc] = 'Canceled'<\/code><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
\n
\"\"Written by Derik Hammer of SQL Hammer<\/a><\/strong><\/p>\n
Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around high-availability<\/a>, disaster recovery<\/a>, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and\u00a0entrepreneurial ventures.<\/p>\n
Derik gives the SQL community credit for plugging the gaps in his knowledge when he was a junior DBA and, now that his skills have matured, started SQLHammer.com<\/a> as one small way to give back and continue the cycle of shared learning.<\/p>\n
Derik is the owner and lead author of SQL Hammer, a Microsoft SQL Server resource<\/a>.<\/p>\n
For more information, visit http:\/\/www.sqlhammer.com<\/a>. Follow Derik on Twitter<\/a> for SQL tips and chat<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
The SQL Server alert system does a great job of handling notifications for SQL Agent job failures but if want to know if a job is hung, taking longer than expected, or maybe it\u2019s disabled and hasn\u2019t been running, we will need to put in a bit of effort ourselves. This first query will return […]<\/p>\n","protected":false},"author":1,"featured_media":525,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-59","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/59","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=59"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/59\/revisions"}],"predecessor-version":[{"id":322,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/59\/revisions\/322"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media\/525"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=59"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=59"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=59"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}