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)	추적 파일의 경로입니다. 이 값은 행 집합 추적의 경우 Null입니다.
max_size	bigint	최대 추적 파일 크기 한도(MB)입니다. 이 값은 행 집합 추적의 경우 Null입니다.
stop_time	datetime	실행 중인 추적을 중지할 시간입니다.
max_files	int	최대 롤오버 파일 수입니다. 최대값을 설정하지 않으면 이 값은 Null입니다.
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	마지막 추적 파일 위치입니다. 이 값은 행 집합 추적의 경우 Null입니다.
reader_spid	int	행 집합 추적 판독기 세션 ID입니다. 이 값은 파일 추적의 경우 Null입니다.
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