기본 추적(default tace) 활성화 및 로그 확인

기본 추적(default tace) 활성화 및 로그 확인

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server를 운영하는데 있어 DBA의 업무 중 하나는 여러 가지 시스템 정보를 수집하는 것이다. 수집된 정보는 어떤 문제가 발생하였을 때 원인을 규명하는 중요한 단서로 활용한다. 대부분의 운영 환경을 보면 기본 정보를 수집하지도 않지만 어떤 문제가 발생하였을 때 기록이 삭제되어 원인을 찾기 힘든 경우가 많다.

 

이번 포스트에서는 기본적으로 수집 해야 하는 정보들과 이를 보관하는 방법에 대해서 알아본다.

 

SQL Server의 기본 추적은 SQL Server 2005부터 도입 되었으며 SQL Server 2012에서는 system_health 라는 확장 이벤트로도 수집 할 수 있다. 데이터베이스의 오브젝트 변경, 보안 감사, 데이터베이스 오류 및 발생하는 주요 이벤트 등 많은 정보를 기록하기 때문에 그때 무슨 일이 있었는지 알아낼 수 있는 훌륭한 정보로 활용된다.

 

기본 추적 파일은 시스템 정의 추적으로 5개의 파일로 구성되어 있으며 약 20MB 정도의 각 폴더에 기록된다. 이 파일은 많은 수의 이벤트를 기록하는 동안 순환 버퍼로 사용된다. 따라서 순환버퍼 정보는 오래된 데이터의 경우 삭제되기 때문에 OLTP 시스템에서는 순환 버퍼의 주기가 짧아 문제가 될 수 있다.(성능상의 문제는 아님)

 

 

[기본 추적(default trace)이 활성 확인]

다음 스크립트를 실행하여 기본 추적의 활성화 여부를 확인 할 수 있다.

EXEC sp_configure 'show advanced options', 1;   Create table #Options (name Varchar(68), minimum int, maximum int, config_value int, run_value int)   insert into #Options execute sp_configure   Select case when exists     (         select *         from #Options         where name like 'default trace enabled' and run_value=1     )     then 'Enabled' else 'disabled' end

 

 

 

[기본 추적 활성화 하기]

기본 추적이 활성화되어 있지 않을 경우 다음 스크립트를 통하여 활성화 할 수 있다.

EXEC master.dbo.sp_configure 'allow updates', 1;   EXEC master.dbo.sp_configure 'show advanced options', 1;   EXEC master.dbo.sp_configure 'default trace enabled', 1;   RECONFIGURE WITH OVERRIDE;   EXEC master.dbo.sp_configure 'show advanced options', 0;   EXEC master.dbo.sp_configure 'allow updates', 0;   RECONFIGURE WITH OVERRIDE;

 

 

 

[기본 추적 항목 확인]

다음 스크립트를 사용하여 기본적으로 추적되고 있는 항목을 확인 할 수 있다.

SELECT distinct e.name AS EventName FROM fn_trace_geteventinfo(1) evi     JOIN sys.trace_events e ON evi.eventid = e.trace_event_id

 

Database events

• Data file auto grow
• Data file auto shrink
• Database mirroring status change
• Log file auto grow
• Log file auto shrink

Errors and warnings

• Errorlog
• Hash warning
• Missing Column Statistics
• Missing Join Predicate
• Sort Warning

Full text events

• FT Crawl Aborted
• FT Crawl Started
• FT Crawl Stopped

Object events

• Object Altered
• Object Created
• Object Deleted

Security audit events

• Audit Add DB user event
• Audit Add login to server role event
• Audit Add Member to DB role event
• Audit Add Role event
• Audit Add login event
• Audit Backup/Restore event
• Audit Change Database owner
• Audit DBCC event
• Audit Database Grant, Deny, Revoke
• Audit Login Change Property event
• Audit Login Failed
• Audit Login GDR event
• Audit Schema Object GDR event
• Audit Schema Object Take Ownership
• Audit Server Starts and Stops

 

 

[기본 추적 파일 저장 경로 확인]

기본 추적은 .trc 파일이며 SQL Profiler 또는 sys.fn_trace_gettable 시스템 함수를 통하여 열어 볼 수 있다. 다음 스크립트는 기본 추적파일이 기록되는 파일의 경로를 확인 할 수 있다.

SELECT     REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)), 256)) AS [DefaultTracePath] FROM sys.traces WHERE is_default = 1

 

 

 

[최근 추적파일 정보 확인]

다음 스크립트를 통하여 마지막 추적파일의 정보를 확인 할 수 있다.

SELECT * FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1 f.[value] FROM sys.fn_trace_getinfo(NULL) f WHERE f.property = 2 )), DEFAULT) T JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id

 

 

 

[5분 동안의 경고 이벤트 조회]

이벤트는 계속해서 발생하고 순환버퍼에 기록된다. 다음 스크립트를 이용하여 5분 간격으로 발생한 이벤트(Hash Warning, Sort Warnings) 정보와 발생 횟수를 알아 보자.

SELECT     TE.name AS [EventName] , V.subclass_name , T.DatabaseName , T.ApplicationName , COUNT(*) AS TotalCount FROM     dbo.fn_trace_gettable((SELECT                              REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)), 256)) + 'log.trc'                             FROM sys.traces          WHERE is_default = 1 ), DEFAULT ) T     JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id JOIN sys.trace_subclass_values V ON V.trace_event_id = TE.trace_event_id AND V.subclass_value = T.EventSubClass WHERE StartTime > DATEADD(mi,-5,GETDATE()) AND (TE.name = 'Hash Warning' OR TE.name = 'Sort Warnings') GROUP BY TE.name , V.subclass_name , T.DatabaseName , T.ApplicationName

 

스크립트를 이용하여 기본 추적을 정의할 수 있다. 스크립트는 서버 추적을 실행하고 결과를 파일로 저장한다. 사용자는 파일의 크기와 위치를 정의 할 수 있다. 단점은 우리가 SQL 서버 테이블에 직접 기록을 할 수 없다. 그러나 다음 스크립트를 사용하여 정보를 가져와 기록 할 수 있다.

SELECT * INTO trace_table FROM ::fn_trace_gettable('c:\my_trace.trc', default)

 

 

[추적 파일 수집 및 집계]

운영하는 서버가 여러 대 라면 한군데에서 추적 파일을 수집하고 이를 분석하려고 할 것이다. 이때 .trc 파일을 복사하여 다양한 이벤트 필터링으로 검사 할 수 있다. dbo.fn_trace_gettable 함수를 사용하여 SQL Server 테이블로 추적파일을 가져오며 집계 할 수 있다.

DECLARE @traceFilesLocation VARCHAR(256) SET @traceFilesLocation = '\\myMachine\c$\Robocopy\Destination\'   SELECT     TE.name AS [EventName] , V.subclass_name , T.DatabaseName ,     T.ApplicationName ,     COUNT(*) AS TotalCount FROM dbo.fn_trace_gettable(( @traceFilesLocation + 'log.trc' ), DEFAULT) T     JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id     JOIN sys.trace_subclass_values V ON V.trace_event_id = TE.trace_event_id AND V.subclass_value = T.EventSubClass WHERE StartTime > DATEADD(hh, 24, GETDATE()) AND (TE.name = 'Hash Warning' OR TE.name = 'Sort Warnings') GROUP BY TE.name , V.subclass_name , T.DatabaseName , T.ApplicationName

 

 

자신이 운영하는 서버의 기본 추적 파일이 순환버퍼의 의해 덮어 쓰여지기 전에 주기적으로 정보를 수집하여 문제 발생시 참고할 수 있는 자료로 활용하자.

 

기본 추적장치를 이용하여 매일 감사 보고서나 변경된 개체에 대한 검사, 기타 필요한 정보를 확인할 수 있도록 한다면 편리하게 정보를 확인 할 수 있다.

 

 

[참고자료]

http://technet.microsoft.com/ko-kr/library/ms175513.aspx

https://www.simple-talk.com/sql/database-administration/collecting-the-information-in-the-default-trace/

 

 

2013-08-13 / 강성욱 / http://sqlmvp.kr