Agent Job相关的系统表

参考：

http://www.cnblogs.com/CareySon/p/5262311.html

 

msdb中，有三张与Agent Job相关的系统表，需要了解一下

　　msdb.dbo.sysjobs
　　msdb.dbo.sysjobactivity
　　msdb.dbo.sysjobhistory

 

有些作业遇到问题执行时间过长，可以用脚本可以根据历史记录，找出执行时间过长的作业，在监控中就可以及时发现这些作业并尽早解决。

如果作业经常出现问题，可以使用该脚本监控尽早发现问题。

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%"