[DBA][MsSQL] 자주 쓰는 쿼리 - Agent 조회 쿼리

Intro

안녕하세요.

초보 DBA 다뉴 입니다.

 

오늘은 DBA 라면 자주 쓰게 될

MsSQL의 작업 Agent를 확인해보는 쿼리에 대해서 알아보도록 하겠습니다.

---

알아두기

Agent 란?

Job, Schedule이라고도 불리며,

MsSQL에 Agent를 통해 일정 등록을 해두어 스케쥴링을 할 수 있습니다.

쿼리 실행, 백업, 프로시저 실행, SSIS, 파워쉘 스크립트 등 많은 작업을 할 수 있습니다.

 

간단한 쿼리 설명

수많은 Agent를 관리하게 되는데, 어떤 Agent 스케줄이 어떤 일정으로 돌고 있는지 확인하기 위한 쿼리입니다.

에이전트 사용정보를 저장하는 시스템 테이블들을 활용하여 작성된 쿼리입니다.

 

각 테이블들의 내용이 궁금하시다면 아래 링크의 공식 문서를 참고해주세요!

반응형

SQL Server 에이전트 테이블(Transact-SQL) - SQL Server

---

Query

Agent 탐색 쿼리

select name, run_requested_date, next_scheduled_run_date, recurrence, frequency, description, last_run_duration
  from (select row_number() over (partition by c.name order by c.run_requested_date desc) as rn,
               c.name, 
               c.run_requested_date, 
               c.next_scheduled_run_date, 
               case freq_type 
                 when 4 then '매 ' + cast(freq_interval as varchar(3)) + '일'
                 when 8 then '매 ' + cast(freq_recurrence_factor as varchar(3)) + '주 ' 
                   + case when freq_interval & 1 = 1 then '일요일' else '' end
                   + case when freq_interval & 2 = 2 then ', 월요일' else '' end
                   + case when freq_interval & 4 = 4 then ', 화요일' else '' end
                   + case when freq_interval & 8 = 8 then ', 수요일' else '' end
                   + case when freq_interval & 16 = 16 then ', 목요일' else '' end
                   + case when freq_interval & 32 = 32 then ', 금요일' else '' end
                   + case when freq_interval & 64 = 64 then ', 토요일' else '' end
                 when 16 then '매월 ' + cast(freq_recurrence_factor as varchar(3)) + '일'
                 when 32 then '매 ' + cast(freq_recurrence_factor as varchar(3)) + ' 개월마다 ' 
                   + case freq_relative_interval
                       when 1 then '첫번째'
                       when 2 then '두번째'
                       when 4 then '세번째'
                       when 8 then '네번째'
                       when 16 then '마지막' 
                     end + ' ' 
                   + case freq_interval 
                       when 1 then '일요일'
                       when 2 then '월요일'
                       when 3 then '화요일'
                       when 4 then '수요일'
                       when 5 then '목요일'
                       when 6 then '금요일'
                       when 7 then '토요일'
                       when 8 then '일요일'
                       when 9 then '주중'
                       when 10 then '주말'
                     end
               end as recurrence, 
               case freq_subday_type 
                 when 1 then stuff(stuff(right('000000' + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') + '에 발생'
                 when 2 then '매일 ' + cast(freq_subday_interval as varchar(3)) + '초('+ stuff(stuff(right('000000' + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') + ' 에서 '
                   + stuff(stuff(right('000000' + cast(active_end_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') + ' 사이) 간격으로 발생'
                 when 4 then '매일 ' + cast(freq_subday_interval as varchar(3)) + ' 분(' + stuff(stuff(right('000000' + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') + ' 에서 ' 
                   + stuff(stuff(right('000000' + cast(active_end_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')+ ' 사이) 간격으로 발생'
                 when 8 then '매일 ' + cast(freq_subday_interval as varchar(3)) + ' 시간('+ stuff(stuff(right('000000' + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') + ' 에서 ' 
                   + stuff(stuff(right('000000' + cast(active_end_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')+ ' 사이) 간격으로 발생' 
               end as frequency, 
               d.description, 
               'last_run_duration' = stuff(stuff(replace(str(lastrun.run_duration,6,0),' ','0'),3,0,':'),6,0,':')
          from msdb.dbo.SYSSCHEDULES as a
         inner join msdb.dbo.SYSJOBSCHEDULES  as b
            on a.schedule_id = b.schedule_id
         inner join (select j.job_id,
                            j.name,  
                            ja.run_requested_date,                
                            ja.next_scheduled_run_date
                       from msdb.dbo.SYSJOBACTIVITY as ja 
                       left outer join msdb.dbo.SYSJOBHISTORY as jh 
                         on ja.job_history_id = jh.instance_id
                      inner join msdb.dbo.SYSJOBS_VIEW as j 
                         on ja.job_id = j.job_id  
                      where enabled = 1
                    ) c
            on b.job_id = c.job_id
         inner join msdb.dbo.SYSJOBS as d
            on b.job_id = d.job_id
          left outer join (select j1.job_id, 
                                  j1.run_duration, 
                                  j1.run_date, 
                                  j1.run_time, 
                                  j1.message
                             from msdb.dbo.SYSJOBHISTORY as j1
                            where instance_id = (select max(instance_id) 
                                                   from msdb.dbo.SYSJOBHISTORY j2 
                                                  where j2.job_id = j1.job_id
                                                )
                          ) as lastrun
            on b.job_id = lastrun.job_id
         where a.enabled = 1
       ) as a
 where a.rn = 1
 order by name

컬럼 설명

• name : Agent 명칭
• run_requested_date : 최근 실행 일시
• next_scheduled_run_date : 다음 실행 일시
• recurrence : 실행 주기
• frequency : 실행 시간
• description : Agent 코멘트
• last_run_duration : 마지막 실행 소요시간

---

Outro

Agent내용들을 알아보는 쿼리에 대해 알아봤습니다.

각종 작업하는 중에 Agent들이 언제 실행되는지 실행소요시간은 어떻게되는지 확인이 필요할 때가 있는데요.

이 쿼리로 쉽게 정리하게 되었네요.

 

많은 도움이 되었으면 합니다. 감사합니다.