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

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


Наиболее часто встречающиеся слова в статье:

[substring] [''00000000''] [VARCHAR] [last_run_duration] [run_duration] [step_id] [step_name] [last_run_date] [job_id=h] [sysjobhistory]


Статья:

 use msdb

SELECT
h.[name]
,h2.[step_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
,h2.step_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.[step_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
,h2.step_id

select h3.name,h3.step_name,h3.last_run_duration,h3.MIN_run_duration,h3.MAX_run_duration,h3.last_run_date
,h3.[last_date ччммсс]
,(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]
,(select top 1 h0.message from sysjobhistory h0 (NOLOCK)where h0.job_id=h3.job_id and h3.step_id = h0.step_id order by h0.instance_id DESC ) as [STATUS_STEP]
from #status h3 order by h3.name
drop table #status