Получить список задач со статусом исполнения с групировкой по заданию

Административные скрипты MS SQL > Получить список задач со статусом исполнения с групировкой по заданию
23.08.2016 11:58:50



Статья:

use msdb

SELECT
h.[name]

,h2.last_run_date
, last_run_time as [last_date ччммсс]

,round( (cast(substring(right('00000000'+ cast(h2.last_run_duration AS VARCHAR(16)), 8), 1, 4) as int) * 60)+cast(substring(right('00000000'+ cast(h2.last_run_duration AS VARCHAR(16)), 8), 5, 2) as int) + cast(substring( right('00000000'+ cast(h2.last_run_duration AS VARCHAR(16)), 8), 7, 2) as float) / 60, 2) AS [last_run_duration]
,round( (cast(substring(right('00000000'+ cast(MIN(h3.run_duration ) AS VARCHAR(16)), 8), 1, 4) as int) * 60)+cast(substring(right('00000000'+ cast(MIN(h3.run_duration ) AS VARCHAR(16)), 8), 5, 2) as int) + cast(substring( right('00000000'+ cast(MIN(h3.run_duration ) AS VARCHAR(16)), 8), 7, 2) as float) / 60, 2) AS MIN_run_duration
,round( (cast(substring(right('00000000'+ cast(MAX(h3.run_duration ) AS VARCHAR(16)), 8), 1, 4) as int) * 60)+cast(substring(right('00000000'+ cast(MAX(h3.run_duration ) AS VARCHAR(16)), 8), 5, 2) as int) + cast(substring( right('00000000'+ cast(MAX(h3.run_duration ) AS VARCHAR(16)), 8), 7, 2) as float) / 60, 2) AS MAX_run_duration
,h.job_id

INTO #status
FROM [sysjobs] h (NOLOCK)
join sysjobsteps h2 (NOLOCK) ON h2.job_id=h.job_id
JOIN sysjobhistory h3 (NOLOCK) ON h3.job_id=h.job_id AND h3.step_id = h2.step_id
JOIN sysjobschedules h4 (NOLOCK) ON h4.job_id=h.job_id
WHERE h.[enabled]=1 --and h.[name] not like '%backup%'
GROUP BY
h.[name]

,h2.last_run_date
,round( (cast(substring(right('00000000'+ cast(h2.last_run_duration AS VARCHAR(16)), 8), 1, 4) as int) * 60)+cast(substring(right('00000000'+ cast(h2.last_run_duration AS VARCHAR(16)), 8), 5, 2) as int) + cast(substring( right('00000000'+ cast(h2.last_run_duration AS VARCHAR(16)), 8), 7, 2) as float) / 60, 2)
,last_run_time
,h.job_id


select h3.name,sum(h3.last_run_duration) last_run_duration , sum(h3.MIN_run_duration) MIN_run_duration ,sum(h3.MAX_run_duration) MAX_run_duration ,min(h3.last_run_date) [RUN_DATE]
,min(h3.[last_date ччммсс]) [RUN_ччммсс]
,(select top 1 h00.message from sysjobhistory h00 (NOLOCK)where h00.job_id=h3.job_id order by h00.instance_id DESC ) as [STATUS_JOB]
from #status h3
group by h3.name,h3.job_id
order by h3.name
drop table #status