有些时候,有些作业遇到问题执行时间过长,因此我写了一个脚本可以根据历史记录,找出执行时间过长的作业,在监控中就可以及时发现这些作业并尽早解决,代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT sj.name , sja.start_execution_date,DATEDIFF (SECOND ,sja.start_execution_date,GETDATE() ) AS ExecutedMin,ja.AvgRuntimeOnSucceed FROM msdb.dbo.sysjobactivity AS sja INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id INNER join ( SELECT job_id, AVG ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100) + NULLIF(0,STDEV ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100)) AS 'AvgRuntimeOnSucceed' FROM msdb.dbo.sysjobhistory WHERE step_id = 0 AND run_status = 1 GROUP BY job_id) ja ON sj.job_id = ja.job_id WHERE sja.start_execution_date IS NOT NULL --作业有开始 AND sja.stop_execution_date IS NULL --作业没结束 AND sja.start_execution_date>DATEADD(DAY,-2,GETDATE()) --作业2天内开始 -- AND DATEDIFF (SECOND ,sja.start_execution_date,GETDATE() )>ja.AvgRuntimeOnSucceed *1.5 --作业执行时间比历史平均时间超了50%" |
如果作业经常出现问题,可以使用该脚本监控尽早发现问题。
发布者:柚子,转转请注明出处:https://ityouzi.com/archives/sql-sysjobactivity.html