데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

기본 추적(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
1851 Sys.dm_os_performance_counter 해석하기 jevida(강성욱) 2016.10.07 2306
1850 프로파일러를 이용한 중첩된 프로시저 디버깅 jevida(강성욱) 2016.10.07 1560
1849 SAN 스토리지 성능 모니터 - SAN 스토리지를 사용하는 경우 성능 카운터를 어떻게 모니터링 할까? jevida(강성욱) 2016.10.07 1743
1848 저장된 Plan Cache 확인 및 활용 jevida(강성욱) 2016.10.07 4606
1847 Xp_fixeddrives 세부 정보 확인하기 jevida(강성욱) 2016.10.07 1744
1846 강제 매개변수화로 인한 성능 저하 사례 jevida(강성욱) 2016.10.07 1562
1845 파라메터 스니핑과 데이터 스큐 jevida(강성욱) 2016.10.07 1528
1844 DBCC CHECKDB 버그 및 해결 방법 jevida(강성욱) 2016.10.07 1540
1843 NOLOCK HINT 이해 jevida(강성욱) 2016.10.07 7854
1842 인증으로부터 분리된 사용자 방지 jevida(강성욱) 2016.10.07 2214
1841 비관리자 계정으로 쿼리 계획 보기 jevida(강성욱) 2016.10.07 1243
1840 SSMS 폴링 간격 구성 jevida(강성욱) 2016.10.07 1772
1839 Deadlock 감지하여 알림하기 jevida(강성욱) 2016.10.07 1782
1838 Suspect_pages 테이블 이해 및 관리 jevida(강성욱) 2016.10.07 1371
1837 SSRS SocketException jevida(강성욱) 2016.10.07 1473
1836 파티션 분할 시 I/O 최소화 하기 jevida(강성욱) 2016.10.07 1862
1835 대량 BCP 작업 시 발생하는 오류 (665, 1450, 33) jevida(강성욱) 2016.10.07 1651
» 기본 추적(default tace) 활성화 및 로그 확인 jevida(강성욱) 2016.10.07 1392
1833 SQL Server ALTER TABLE syntax diagrams jevida(강성욱) 2016.10.07 1161
1832 SQL Server Performance Counter Guidance jevida(강성욱) 2016.09.30 2534





XE Login