поиск пересекающихся джобов(jobs)

Административные скрипты MS SQL > поиск пересекающихся джобов(jobs)
05.07.2013 15:33:53


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

[run_datetime] [datetime] [substring] [varchar] [end_datetime] [step_id] [CONVERT] [duration] [ convert] [run_date]


Статья:

use [msdb]
GO
with ste as(
select 
	jh.job_id,
	jh.step_id,
	convert(datetime,cast(jh.run_date as varchar(8)),112)+CONVERT(datetime,Tfull.T,108) as run_datetime,	
	convert(datetime,cast(jh.run_date as varchar(8)),112)+CONVERT(datetime,Tfull.T,108)+CONVERT(datetime,Tdfull.T,108) as end_datetime,
	CONVERT(datetime,Tdfull.T,108) as duration
from dbo.sysjobhistory as jh
cross apply(select T = right('00000'+cast(jh.run_time as varchar(6)),6))T
cross apply(select T = substring(T.T,1,2))Th
cross apply(select T = substring(T.T,3,2))Tm
cross apply(select T = substring(T.T,5,2))Ts
cross apply(select T = Th.T+':'+Tm.T+':'+Ts.T)Tfull
cross apply(select T = right('00000'+cast(jh.run_duration as varchar(6)),6))Td
cross apply(select T = substring(Td.T,1,2))Tdh
cross apply(select T = substring(Td.T,3,2))Tdm
cross apply(select T = substring(Td.T,5,2))Tds
cross apply(select T = Tdh.T+':'+Tdm.T+':'+Tds.T)Tdfull

where  jh.run_status = 1
and jh.step_id = 0 
)
SELECT ROW_NUMBER() over (partition by convert(varchar,a.run_datetime,112) order by a.run_datetime) as row_num,
	j.name as job_a,	
	a.step_id as job_a_step_id,
	a.run_datetime as job_a_runtime,
	a.end_datetime as job_a_endtime,
	a.duration as job_a_duration,	
	j0.name as job_b,
	a0.step_id as job_b_step_id,
	a0.run_datetime as job_b_runtime,
	a0.end_datetime as job_b_endtime,
	a0.duration as job_b_duration,
	a.job_id as job_a_id,
	a0.job_id as job_b_id
  FROM ste as a
  JOIN ste as a0    ON a.job_id <> a0.job_id
  join dbo.sysjobs as j on j.job_id = a.job_id
  join dbo.sysjobs as j0 on j0.job_id = a0.job_id
   AND a0.end_datetime >= a.run_datetime
   AND a.end_datetime >= a0.run_datetime