我这个脚本每小时运行一次以获得失败工作。 这个脚本的问题是当有一个运行的作业让我说2个小时而且它最终失败时脚本将找不到这个失败的工作。 我能做些什么来获得每小时失败的工作,即使这项工作运行了一个多小时而失败了? THX SELECT j。[name] as job_name, s.step_name, h.run_date, h.run_time, h.message FROM msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id - 和h.step_id = s.step_id WHER h.run_status in(0,2,3) - 失败 AND msdb.dbo.agent_datetime(h.run_date,h.run_time)> =(SELECT DATEADD(MI,-60,GETDATE())) 按msdb.dbo.agent_datetime排序(h.run_date,h.run_time)desc
解决方案
下面的代码适合你(如果工作持续超过一小时,则返回三小时):
SELECT j。[name] as job_name, s.step_name, h.run_date, h.run_time, h.message FROM msdb.dbo.sysjobhistory h INNER JOIN msdb .dbo.sysjobs j ON h.job_id = j.job_id INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id --AND h。 step_id = s.step_id WHERE h.run_status in(0,2,3) - 失败 AND msdb.dbo.agent_datetime(h.run_date,h.run_time)> = Iif(STUFF(STUFF(右(重复('0',8)+ CAST(h.run_duration as varchar(8)),8),3,0,':'),6,0,':' ),9,0,':')< '01:00:01',(SELECT DATEADD(MI,-60,GETDATE())),(SELECT DATEADD(MI, - 180,GETDATE())))命令msdb.dbo.agent_datetime(h.run_date,h.ru n_time)desc如果这篇文章帮助你,请标记为答案
Hi, I've this script that runs every hour to get the failed job. the problem with this script is when there is a job that runs lets says for 2 hour and it's failed in the end the script will not find this failed job. what can i do to get the failed jobs per hour even if the job is run more then an hour and failed? THX SELECT j.[name] as job_name, s.step_name, h.run_date, h.run_time, h.message FROM msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id --AND h.step_id = s.step_id WHERE h.run_status in (0,2,3) -- Failure AND msdb.dbo.agent_datetime (h.run_date,h.run_time) >= (SELECT DATEADD (MI, -60, GETDATE())) order by msdb.dbo.agent_datetime (h.run_date,h.run_time) desc
解决方案The code below works for you (three hours back if the job did last for more than one hour):
SELECT j.[name] as job_name, s.step_name, h.run_date, h.run_time, h.message FROM msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id --AND h.step_id = s.step_id WHERE h.run_status in (0,2,3) -- Failure AND msdb.dbo.agent_datetime (h.run_date,h.run_time) >= Iif(STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(h.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':')<'01:00:01', (SELECT DATEADD (MI, -60, GETDATE())) , (SELECT DATEADD (MI, -180, GETDATE()))) order by msdb.dbo.agent_datetime (h.run_date,h.run_time) descPlease mark as answer if this post helped you
更多推荐
每小时失败的工作
发布评论