获取作业的执行结果历史记录

最新推荐文章于 2025-02-08 10:07:07 发布

原创最新推荐文章于 2025-02-08 10:07:07 发布 · 877 阅读

0 ·

CC 4.0 BY-SA版权

SQL 专栏收录该内容

19 篇文章

订阅专栏

本文介绍了一个简易的作业监控工具，该工具通过定时检查服务器上特定作业的日志文件，并在作业出现错误时向预设邮箱发送通知邮件。使用SQL查询从历史记录中筛选出失败、取消及重试状态的作业。

做了一个作业简单的监控工具，实现对几个服务器的作业进行监控，当作业执行错误时，即时发送邮件到几个指定的邮箱，主要是用timer循环查获作业的历史日志记录。

SELECT [JobName] = JOB.name, [Step] = HIST.step_id, [StepName] = HIST.step_name, [Message] = HIST.message, [Status] = CASE WHEN HIST.run_status = 0 THEN 'Failed' WHEN HIST.run_status = 1 THEN 'Succeeded' WHEN HIST.run_status = 2 THEN 'Retry' WHEN HIST.run_status = 3 THEN 'Canceled' END, [RunDate] = HIST.run_date, [RunTime] = HIST.run_time, [Duration] = HIST.run_duration FROM sysjobs JOB INNER JOIN sysjobhistory HIST ON HIST.job_id = JOB.job_id WHERE HIST.run_date>=CONVERT(CHAR(8),GETDATE()-1,112) AND HIST.run_status<>1

and JOB.name = 'jobnamemodel' and HIST.run_date='rundatemodel' and HIST.run_time between 'runtimemodel1' and 'runtimemodel2' ORDER BY HIST.run_date, HIST.run_time