Query를 이용한 Trace 조작
성능을 수집하기 위해서 사용되는 일반적인 도구인 SQL Server Profiler는 흔히 사용하는 도구입니다.
하지만, Profiler 사용 시 예기치 못한 오류로 갑자기 종료되는 경우 Trace를 중지,종료 할 수 없어 당황하는 경우가 있습니다.
그래서 SSMS에서 쿼리를 이용하여 Trace를 조작하는 방법을 살펴 보겠습니다.
우선 Trace의 상태를 살펴보기 위해서 sys.trace를 이용 합니다.
select * from sys.traces
아래와 같은 결과가 sys.traces에 의해서 출력되며, 각각의 항목은 MSDN에 자세히 나와 있습니다.
항목 별 설명 (https://msdn.microsoft.com/ko-kr/library/ms178579(v=sql.120).aspx)
열 이름 | 데이터 형식 | 설명 |
|---|---|---|
id | int | 추적 ID입니다. |
status | int | 추적 상태입니다. 0 = 중지됨 1 = 실행 중 |
path | nvarchar(260) | |
max_size | bigint | |
stop_time | datetime | 실행 중인 추적을 중지할 시간입니다. |
max_files | int | |
is_rowset | bit | 1 = 행 집합 추적입니다. |
is_rollover | bit | 1 = 롤오버 옵션이 설정되어 있습니다. |
is_shutdown | bit | 1 = 종료 옵션이 설정되어 있습니다. |
is_default | bit | 1 = 기본 추적입니다. |
buffer_count | int | 추적에서 사용하는 메모리 내 버퍼 수입니다. |
buffer_size | int | 각 버퍼의 크기(KB)입니다. |
file_position | bigint | |
reader_spid | int | |
start_time | datetime | 추적 시작 시간입니다. |
last_event_time | datetime | 마지막 이벤트 발생 시간입니다. |
event_count | bigint | 발생한 총 이벤트 수입니다. |
dropped_event_count | int | 삭제된 총 이벤트 수입니다. |
현재 sys.traces를 보면 1,2번으로 개채가 2개가 있습니다. 1번의 경우 SQL Server에서 기본적으로 걸려있는 Trace이므로, 일반적으로 수집되도록 나두고 있습니다.
id 1번 traces (http://m.blog.naver.com/taekani/130104336424)
지금 제가 걸어둔 Trace는 id가 2번 입니다. 이때 프로파일러가 문제가 있어 종료되었다고 해보겠습니다.
분명히 현재 프로세스는 종료되었으나, sys.traces를 보면 아직 종료되지 않았음을 볼수 있습니다.
이때 필요한 것이 쿼리를 통한 Trace의 조작 입니다. 구문은 아주 간단합니다.
declare @a int -- trace id declare @b int -- trace status set @a = 2 set @b = 0 exec sp_trace_setstatus @a,@b
각각의 파라미터는 다음과 같스니다.
㉠Trace id : Trace의 번호를 뜻 합니다. (제가 종료 하려는 번호는 2번이니 2버를 입력합니다.
㉡Trace status
0 : Trace를 중지합니다.
1 : Trace를 활성화 합니다.
2 : Trace를 종료합니다.
Trace Status의 경우 1에서 즉시 2로 넘어갈 수 없습니다. 종료를 위해서 우선 Trace를 중지시켜줘야 합니다.
status의 값을 0으로 만들어 중지 시킨 이후에 2로 변경하여 완전히 종료해야 합니다. 0->1로 즉시 변경 할 경우 아래와 같은 에러가 나타납니다.
해당 시스템 저장 프로시저를 이용하여, Traces를 수집 할 시에 예기치 못한 종료 상황에 당황하지 않고 대처 할 수 있습니다.
감사합니다.
참조 : https://msdn.microsoft.com/en-us/library/ms176034.aspx