tag:blogger.com,1999:blog-370183193676959553.post6978580303260039918..comments2024-03-28T17:02:21.297+05:30Comments on SansSQL: T-SQL Query to find last run status of scheduled JobsSandesh Seguhttp://www.blogger.com/profile/12465388540851337091noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-370183193676959553.post-11842596130351275202018-04-19T11:05:05.868+05:302018-04-19T11:05:05.868+05:30This query returns the failed/cancelled jobs as pe...This query returns the failed/cancelled jobs as per the last run status. <br />USE msdb<br />GO<br />if OBJECT_ID('tempdb..#temptbl1') is not null drop table #temptbl1<br />go<br />SELECT DISTINCT sjh.server, SJ.Name AS JobName, SJH.step_id,sjh.step_name,sjh.message,<br />DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME) AS [LastTimeJobRanOn] ,<br />CASE SJH.run_status<br />WHEN 0 THEN 'Failed&#Unknownhttps://www.blogger.com/profile/10032087233694367215noreply@blogger.comtag:blogger.com,1999:blog-370183193676959553.post-73385736098535675562017-01-10T16:29:04.562+05:302017-01-10T16:29:04.562+05:30Your query only works if the job history hasn'...Your query only works if the job history hasn't been cleaned yet. Job Activity Monitor can still retrieve the status and last run date, even after the job history has been cleaned. For this it uses dbo.sysjobservers. A query that gives you the same results is:<br /><br />select<br /> sj.name,<br /> sjs.last_run_outcome, -- 0 = fail, 1 = succeed, 2 = cancel<br /> Anonymoushttps://www.blogger.com/profile/01394349260652287085noreply@blogger.comtag:blogger.com,1999:blog-370183193676959553.post-24503855068524965672014-06-08T23:14:01.957+05:302014-06-08T23:14:01.957+05:30Thx, I like the scriptThx, I like the scriptT. Johnsonhttps://www.blogger.com/profile/13825423337059290497noreply@blogger.com