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

SQL Server 2005 이상

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13

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 스케줄 조회

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

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 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

--======================================= -- 해당 시간에 실행되는  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