기본 추적(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/

 

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 20076
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 12120
» 기본 추적(default tace) 활성화 및 로그 확인 jevida(강성욱) 2016.10.07 1452
1833 SQL Server ALTER TABLE syntax diagrams jevida(강성욱) 2016.10.07 1188
1832 SQL Server Performance Counter Guidance jevida(강성욱) 2016.09.30 2612
1831 SQL Server CREATE TABLE syntax diagrams jevida(강성욱) 2016.09.30 1456
1830 SQL Server 2012 Sp1 설치 이슈 및 해결 jevida(강성욱) 2016.09.30 1647
1829 SSD에서 DBCC CHECKDB 성능 벤치마킹 jevida(강성욱) 2016.09.30 1980
1828 Collation에 따른 ALTER DATABASE 실패 jevida(강성욱) 2016.09.30 1998
1827 LDF 파일이 잘리지 않는 이유 jevida(강성욱) 2016.09.30 2161
1826 Tempdb 경합 확인 및 해결 (Tempdb Contention) jevida(강성욱) 2016.09.30 2286
1825 SQL Server IO 병목 확인과 오해 jevida(강성욱) 2016.09.30 2637
1824 SQL Version에 따른 sp_prepare 정보 반환 jevida(강성욱) 2016.09.30 1770
1823 DReplay 활성 세션 초과 에러 jevida(강성욱) 2016.09.30 1586
1822 저장 프로시저 내 임시 테이블 사용과 프로시저 재컴파일 jevida(강성욱) 2016.09.30 1988
1821 NUMA 노드와 추척플래그 8048 jevida(강성욱) 2016.09.30 861
1820 온라인 인덱스 리빌드와 조각화 증가 jevida(강성욱) 2016.09.30 1661
1819 VARCHAR(MAX) and NTEXT 쿼리 성능 jevida(강성욱) 2016.09.30 5956
1818 CPU 리소스 상태에 따른 병렬 처리 제한 jevida(강성욱) 2016.09.30 1146
1817 매개변수 값의 변경과 SQL 서버 성능 저하 jevida(강성욱) 2016.09.30 1325
1816 IN 절 사용시 예기치 못한 액세스 위반과 SQL Server 종료 jevida(강성욱) 2016.09.30 1086
1815 SQL 버전과 CLR (.NET Framework 버전에 따른 오류) jevida(강성욱) 2016.09.30 1297





XE Login