T-SQL - Last Job Run Duration

最新推荐文章于 2020-06-15 16:19:58 发布

xxdxxd

最新推荐文章于 2020-06-15 16:19:58 发布

阅读量179

点赞数

文章标签： SQL J#

select
 job_name,
 run_datetime,
SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
SUBSTRING(run_duration, 5, 2) AS run_duration
from
(
select
  job_name,
  DATEADD(hh, -7, run_datetime) as run_datetime,
  run_duration = RIGHT('000000' + CONVERT(varchar(6), h.run_duration), 6)
from
 (
select
   j.name as job_name,
   run_datetime = max(CONVERT(DATETIME, RTRIM(run_date)) + 
    (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4)
from msdb..sysjobhistory h
inner join msdb..sysjobs j
on h.job_id = j.job_id
group by j.name
 ) t
inner join msdb..sysjobs j
on t.job_name = j.name
inner join msdb..sysjobhistory h
on j.job_id = h.job_id and
   t.run_datetime = CONVERT(DATETIME, RTRIM(h.run_date)) + (h.run_time * 9 + h.run_time % 10000 * 6 + h.run_time % 100 * 10) / 216e4
) dt