SQL脚本作业持续时间异常 [英] SQL script Job duration unusual

问题描述

如果任何SQL作业在第一天运行一小时，如果运行超过一小时的第二天意味着需要知道作业持续时间为unusal.Need SQL Script。

If any SQL job ran at first day one hour,if second day running more than one hour means need to know job duration unusal.Need SQL Script.

问候，

Niranjan B

Niranjan B

推荐答案

Hi Niranjan，

Hi Niranjan,

我相信以下SQL脚本应该足以让你入门：

I believe the following SQL script should be good enough to get you started:

select j.job_id, j.name as job_name
, count(*) as last_runs_checked
, avg(run_duration_seconds) AS avg_run_duration_sec
, min(run_duration_seconds) AS min_run_duration_sec
, max(run_duration_seconds) AS max_run_duration_sec
, (min(run_duration_seconds) + max(run_duration_seconds)) / 2 AS median_run_duration_sec
from msdb..sysjobs as j
cross apply
(
	select top (10) instance_id, datediff(second, '1970-01-01', msdb.dbo.agent_datetime(19700101, run_duration)) as run_duration_seconds
	from msdb..sysjobhistory as jh
	where jh.job_id = j.job_id
	and step_id = 0 -- (Job outcome)
	order by msdb.dbo.agent_datetime(run_date, run_time) DESC
) as last10runs
group by j.job_id, j.name

这篇关于SQL脚本作业持续时间异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！