[T-SQL] JOb 수행 시간 완료 계산

SQL Server 2005 이상

1. JOB의 수행 완료 시간 계산

SELECT  a.name,  b.run_date, convert(varchar(4),run_time/10000) + ':' + convert(varchar(4),run_time/100%100) + ':' + convert(varchar(4),run_time%100) as run_time
		,convert(varchar(4),run_duration/10000) + ':' + convert(varchar(4),run_duration/100%100) + ':' + convert(varchar(4),run_duration%100) as run_duration
		,convert(int, (run_duration/10000 * 3600 ) + ((run_duration/100%100) * 60) + (run_duration%100 )) as duration_sec
		,convert(DATETIME, RTRIM(run_date)) +  (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4 as start_date
		,dateadd(ss, convert(int, (run_duration/10000 * 3600 ) + ((run_duration/100%100) * 60) + (run_duration%100 ))
					,CONVERT(DATETIME, RTRIM(run_date)) +  (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4 ) as end_date
FROM msdb.dbo.sysjobs as a with (nolock)
	inner join msdb.dbo.sysjobhistory as b with (nolock)  on a.job_id = b.job_id
where 
	--and b.job_id = ''
	b.run_date >= 20100709 and b.run_date < 20100719
	and b.step_id = 0
order by b.run_date, b.run_time

2. JOB 스케줄 조회

select
    j.job_id
,   J.name
,   S.name as '일정'
,	CASE S.freq_type WHEN 1 THEN '한번만'
			WHEN 4 THEN '매일'
			WHEN 8 THEN '매주'
			WHEN 16 THEN '매월'
			WHEN 32 THEN '매월 상대적'
			WHEN 64 THEN 'SQL Server Agent가 시작될때 실행' 
	END as freq_type 
,   s.freq_interval
,   CASE S.freq_subday_type
        WHEN 1 THEN '지정된시간'
        WHEN 2 THEN '초'
        WHEN 4 THEN '분'
        WHEN 8 THEN '시간'
    END AS freq_subday_type
,	S.freq_subday_interval         
,	right('00000' + cast(S.active_start_time as varchar), 6) as '시작시간'
,	right('00000' + cast(S.active_end_time as varchar), 6) as '끝시간'
,	S.active_start_date as 'job 시작일'
,	S.active_end_date as 'job 종료일'
from msdb.dbo.sysjobs J LEFT JOIN sysjobschedules as A on a.job_id = J.job_id
    JOIN msdb.dbo.sysschedules S WITH (NOLOCK) ON A.schedule_id = S.schedule_id
where J.enabled = 1
order by J.name, right('00000' + cast(S.active_start_time as varchar), 6) asc

3. 해당 시간에 실행되는 JOB 

--=======================================
-- 해당 시간에 실행되는  JOB 
-- =======================================
select  J.name
	, S.last_run_date
	, convert(char(10), S.last_run_time, 114) as 'last_run_start_time'
	,S.last_run_time + S.last_run_duration as 'last_run_end_time', S.last_run_duration as 'duration_time'
from msdb.dbo.sysjobservers S with (nolock)
  inner join msdb.dbo.sysjobs J with (nolock) on S.job_id = J.job_id
where S.last_run_date = '20081202'
-- 작업 시간 입력
and ( (S.last_run_time between '000000' and '010000' ) or
	(S.last_run_time + S.last_run_duration >= '000000' and S.last_run_time + S.last_run_duration < '010000'))
	and J.enabled = 1
order by J.name


select j.job_id, j.name, h.step_id
	, sum(case when  h.run_status = 1 then 1 else 0 end) as '성공count'
	, sum(case when  h.run_status = 0 then 1 else 0 end) as '실패count'
	 ,max(h.run_date) as run_date, max(h.run_time) as run_time 
	, max((CONVERT ( DATETIME, RTRIM(run_date)) 
		+  ( run_time * 9 + run_time % 10000 * 6  + run_time % 100 * 10  + 25 * run_duration ) / 216e4  ) )  as 종료시간	 
from msdb.dbo.sysjobhistory as h with (nolock)
	join msdb.dbo.sysjobs as j with (nolock) on h.job_id = j.job_id
where run_date = '20100412'
	and run_time >= '210000'
group by j.job_id, j.name, h.step_id
order by j.job_id, h.step_id