반응형
SQL서버의 블랙박스 추적기능
SQL서버가 아무도 모르는 이유로 종료되었을 경우, 종료 직전에 누가 어떤 작업을 했는지,
SQL서버가 왜 깨졌는지를 알기란 쉽지 않습니다. 하지만 SQL서버의 BlackBox 추적 기능을 이용한다면,
SQL서버 종료 이전의 5MB의 작업 내용을 trace파일로 받을 수 있습니다.
자, 그럼 어떻게 블랙박스 추적을 설정하는지 살펴보겠습니다.
우선 sp_trace_create 프로시져를 이용하여 추적을 설정합니다.
여기서 추적 옵션값으로 8을 지정합니다. 온라인 도움말에서 sp_trace_create 를 찾아보시면, option값에 대한 내용
이 나옵니다.
DECLARE @traceid INT;
EXEC sp_trace_create @traceid OUTPUT, @options = 8;
SELECT @traceid AS [Trace Id];
저의 SQL서버에서는 traceid값으로 2를 반환하였습니다.
여기서 중요한 부분은 옵션으로 지정한 8입니다.
이 옵션값 8은 TRACE_PRODUCE_BLACKBOX 라는 옵션이름을 가지며, 이 옵션을 지정하여 블랙박스 기능을 이용하
게 됩니다..
이제 설정된 trace추적에 대한 정보를 확인 해보겠습니다.
SELECT * FROM fn_trace_getinfo (2);
/*
traceid property value
----------- ----------- ----------------------------------------------------------------
2 1 8
2 2 \\?\C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\DATA\blackbox.trc
2 3 5
2 4 NULL
2 5 0
(5개행적용됨)
*/
결과로 나온 다양한 속성들은 각각 다음을 의미합니다.
• 1: 추적옵션. 8은 블랙박스 추적을 의미.
• 2: 추적파일의 파일명. 블랙박스 추적을 위해서는 별도 지정이 불가함.
• 3: 추적파일의 최대크기. 블랙박스 추적을 위해서는 별도 지정이 불가함.
• 4: 추적 중지 시간. NULL 은 수동으로 종료 시까지 중지하지 않음을 의미.
• 5: 현재 추적 상태. 0 은 중지, 1 은 추적중을 의미.
위 추적정보에서 status 값이 0 이므로, 현재 추적 설정은 되었으나, 활성화 되진 않았습니다.
따라서, 아래 명령을 통하여, 추적을 활성화 하도록 합니다.
EXEC sp_trace_setstatus @traceid = 2, @status = 1;
GO
이렇게 해서 블랙 박스 추적이 설정되었습니다.
이제, 실제 SQL서버 중지 시에 정상적으로 추적이 되는지 확인 하기 위해서 SQL서버를 임의로 중지시키고,
다시 시작하도록 하겠습니다.
command창에서 net stop, net start명령으로 SQL서버 서비스를 중지, 시작 시킵니다.
Net stop MSSQLSERVER
Net start MSSQLSERVER
탐색기를 열어서 아래의 위치(SQL2005기준)에 blackbox.trc 파일이 생성되었는지 확인합니다.
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\blackbox.trc'
생성되었다면, fn_trace_gettable 함수를 이용하여 추적파일 내용을 조회해 봅니다.
SELECT * FROM fn_trace_gettable (
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\blackbox.trc',
DEFAULT);
GO
위 과정을 통해서 SQL서버의 블랙박스 추적기능을 이용하실 수 있습니다. 보신 바와 같이 간단한 세팅으로 아주 유용
한 정보를 얻을 수 있습니다. 단, 이 세팅은 SQL서버가 재시작되면, 다시 설정 하셔야 적용되므로, SQL서버 시작시
에 자동으로 등록하는 스크립트를 만들어서 사용하시는 것이 좋을 것입니다.
참고 블로그: https://blogs.msdn.com/sqlserverstorageengine
SQL서버가 아무도 모르는 이유로 종료되었을 경우, 종료 직전에 누가 어떤 작업을 했는지,
SQL서버가 왜 깨졌는지를 알기란 쉽지 않습니다. 하지만 SQL서버의 BlackBox 추적 기능을 이용한다면,
SQL서버 종료 이전의 5MB의 작업 내용을 trace파일로 받을 수 있습니다.
자, 그럼 어떻게 블랙박스 추적을 설정하는지 살펴보겠습니다.
우선 sp_trace_create 프로시져를 이용하여 추적을 설정합니다.
여기서 추적 옵션값으로 8을 지정합니다. 온라인 도움말에서 sp_trace_create 를 찾아보시면, option값에 대한 내용
이 나옵니다.
DECLARE @traceid INT;
EXEC sp_trace_create @traceid OUTPUT, @options = 8;
SELECT @traceid AS [Trace Id];
저의 SQL서버에서는 traceid값으로 2를 반환하였습니다.
여기서 중요한 부분은 옵션으로 지정한 8입니다.
이 옵션값 8은 TRACE_PRODUCE_BLACKBOX 라는 옵션이름을 가지며, 이 옵션을 지정하여 블랙박스 기능을 이용하
게 됩니다..
이제 설정된 trace추적에 대한 정보를 확인 해보겠습니다.
SELECT * FROM fn_trace_getinfo (2);
/*
traceid property value
----------- ----------- ----------------------------------------------------------------
2 1 8
2 2 \\?\C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\DATA\blackbox.trc
2 3 5
2 4 NULL
2 5 0
(5개행적용됨)
*/
결과로 나온 다양한 속성들은 각각 다음을 의미합니다.
• 1: 추적옵션. 8은 블랙박스 추적을 의미.
• 2: 추적파일의 파일명. 블랙박스 추적을 위해서는 별도 지정이 불가함.
• 3: 추적파일의 최대크기. 블랙박스 추적을 위해서는 별도 지정이 불가함.
• 4: 추적 중지 시간. NULL 은 수동으로 종료 시까지 중지하지 않음을 의미.
• 5: 현재 추적 상태. 0 은 중지, 1 은 추적중을 의미.
위 추적정보에서 status 값이 0 이므로, 현재 추적 설정은 되었으나, 활성화 되진 않았습니다.
따라서, 아래 명령을 통하여, 추적을 활성화 하도록 합니다.
EXEC sp_trace_setstatus @traceid = 2, @status = 1;
GO
이렇게 해서 블랙 박스 추적이 설정되었습니다.
이제, 실제 SQL서버 중지 시에 정상적으로 추적이 되는지 확인 하기 위해서 SQL서버를 임의로 중지시키고,
다시 시작하도록 하겠습니다.
command창에서 net stop, net start명령으로 SQL서버 서비스를 중지, 시작 시킵니다.
Net stop MSSQLSERVER
Net start MSSQLSERVER
탐색기를 열어서 아래의 위치(SQL2005기준)에 blackbox.trc 파일이 생성되었는지 확인합니다.
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\blackbox.trc'
생성되었다면, fn_trace_gettable 함수를 이용하여 추적파일 내용을 조회해 봅니다.
SELECT * FROM fn_trace_gettable (
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\blackbox.trc',
DEFAULT);
GO
위 과정을 통해서 SQL서버의 블랙박스 추적기능을 이용하실 수 있습니다. 보신 바와 같이 간단한 세팅으로 아주 유용
한 정보를 얻을 수 있습니다. 단, 이 세팅은 SQL서버가 재시작되면, 다시 설정 하셔야 적용되므로, SQL서버 시작시
에 자동으로 등록하는 스크립트를 만들어서 사용하시는 것이 좋을 것입니다.
참고 블로그: https://blogs.msdn.com/sqlserverstorageengine
반응형