DDL 트리거를 활용한 ERRORLOG에 XEVENT 상태 기록하기

DDL 트리거를 활용한 ERRORLOG에 XEVENT 상태 기록하기

 

• Version : SQL Server 2012

 

SQL Server에 적용되어 있는 XEVENT의 활동을 중지하거나 시작하거나, 또는 생성, 삭제, 수정이 발생 하였을 경우 어떻게 감지 할 수 있을까?

 

다음은 DDL 트리거를 활용하여 XEVENT의 상태 변경이 발생 하였을 경우 이벤트 로그에 남기는 법을 알아 보자.

 

XEVENT를 생성하고 삭제하고 또는 중지 하였을 경우 에로로그 파일에 기록이 남지 않는다.

 

다음은 기존의 확장 이벤트를 삭제하고 등록하는 예제이다.

 

XEVENT를 삭제해 보았다.

DROP EVENT SESSION [system_health] ON SERVER GO

 

그리고 XEVENT를 새로 생성하였다. 아래 스크립트는 시스템헬스 체크를 하는 이벤트 이다.

CREATE EVENT SESSION [system_health] ON SERVER ADD EVENT sqlclr.clr_allocation_failure( ACTION(package0.callstack,sqlserver.session_id)), ADD EVENT sqlclr.clr_virtual_alloc_failure( ACTION(package0.callstack,sqlserver.session_id)), ADD EVENT sqlos.memory_broker_ring_buffer_recorded, ADD EVENT sqlos.memory_node_oom_ring_buffer_recorded( ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack)), ADD EVENT sqlos.scheduler_monitor_deadlock_ring_buffer_recorded, ADD EVENT sqlos.scheduler_monitor_non_yielding_iocp_ring_buffer_recorded, ADD EVENT sqlos.scheduler_monitor_non_yielding_ring_buffer_recorded, ADD EVENT sqlos.scheduler_monitor_non_yielding_rm_ring_buffer_recorded, ADD EVENT sqlos.scheduler_monitor_stalled_dispatcher_ring_buffer_recorded, ADD EVENT sqlos.scheduler_monitor_system_health_ring_buffer_recorded, ADD EVENT sqlos.wait_info( ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text) WHERE ([duration]>(15000) AND ([wait_type]>(31) AND ([wait_type]>(47) AND [wait_type]<(54) OR [wait_type]<(38) OR [wait_type]>(63) AND [wait_type]<(70) OR [wait_type]>(96) AND [wait_type]<(100) OR [wait_type]=(107) OR [wait_type]=(113) OR [wait_type]>(174) AND [wait_type]<(179) OR [wait_type]=(186) OR [wait_type]=(207) OR [wait_type]=(269) OR [wait_type]=(283) OR [wait_type]=(284)) OR [duration]>(30000) AND [wait_type]<(22)))), ADD EVENT sqlos.wait_info_external( ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text) WHERE ([duration]>(5000) AND ([wait_type]>(365) AND [wait_type]<(372) OR [wait_type]>(372) AND [wait_type]<(377) OR [wait_type]>(377) AND [wait_type]<(383) OR [wait_type]>(420) AND [wait_type]<(424) OR [wait_type]>(426) AND [wait_type]<(432) OR [wait_type]>(432) AND [wait_type]<(435) OR [duration]>(45000) AND ([wait_type]>(382) AND [wait_type]<(386) OR [wait_type]>(423) AND [wait_type]<(427) OR [wait_type]>(434) AND [wait_type]<(437) OR [wait_type]>(442) AND [wait_type]<(451) OR [wait_type]>(451) AND [wait_type]<(473) OR [wait_type]>(484) AND [wait_type]<(499) OR [wait_type]=(365) OR [wait_type]=(372) OR [wait_type]=(377) OR [wait_type]=(387) OR [wait_type]=(432) OR [wait_type]=(502))))), ADD EVENT sqlserver.connectivity_ring_buffer_recorded(SET collect_call_stack=(1)), ADD EVENT sqlserver.error_reported( ACTION(package0.callstack,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack) WHERE ([severity]>=(20) OR ([error_number]=(17803) OR [error_number]=(701) OR [error_number]=(802) OR [error_number]=(8645) OR [error_number]=(8651) OR [error_number]=(8657) OR [error_number]=(8902)))), ADD EVENT sqlserver.security_error_ring_buffer_recorded(SET collect_call_stack=(1)), ADD EVENT sqlserver.sp_server_diagnostics_component_result(SET collect_data=(1) WHERE ([sqlserver].[is_system]=(1) AND [component]<>(4))), ADD EVENT sqlserver.xml_deadlock_report ADD TARGET package0.event_file(SET filename=N'system_health.xel',max_file_size=(5),max_rollover_files=(4)), ADD TARGET package0.ring_buffer(SET max_events_limit=(5000),max_memory=(4096)) WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON) GO

 

 

XEVENT의 변경 사항은 어디에서 확인 할 수 있을까? 에러로그에도 기록은 남아 있지 않다.

 

 

다음 스크립트를 실행하여 서버전체의 DDL 트리거를 생성하자. 이벤트 세션의 변화에 대해서 감지하도록 하였다. 그리고 그 내용을 에러로그에 기록하도록 하였다.

CREATE TRIGGER XEventLogging ON ALL SERVER     FOR DDL_EVENT_SESSION_EVENTS   AS   BEGIN     SET NOCOUNT ON;       DECLARE @EventData XML = EVENTDATA();     DECLARE @EventType NVARCHAR(256) = @EventData.value('(EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(256)')     DECLARE @SessionName NVARCHAR(256) = @EventData.value('(EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(256)')     DECLARE @LoginName NVARCHAR(256) = @EventData.value('(EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(256)')     DECLARE @Command NVARCHAR(MAX) = @EventData.value('(EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)');     DECLARE @msg NVARCHAR(440) = CASE         WHEN @EventType = 'CREATE_EVENT_SESSION' THEN 'Extended Event session created. Session Name = ''%s''. Login Name = ''%s''.'         WHEN @EventType = 'ALTER_EVENT_SESSION' AND LOWER(@Command) LIKE LOWER('%STATE%=%START%') THEN 'Extended Event session started. Session Name = ''%s''. Login Name = ''%s''.'         WHEN @EventType = 'ALTER_EVENT_SESSION' AND LOWER(@Command) LIKE LOWER('%STATE%=%STOP%') THEN 'Extended Event session stopped. Session Name = ''%s''. Login Name = ''%s''.'         WHEN @EventType = 'DROP_EVENT_SESSION' THEN 'Extended Event session dropped. Session Name = ''%s''. Login Name = ''%s''.' END   RAISERROR(@msg, 10, 1, @SessionName, @LoginName) WITH LOG;   END   GO

 

 

트리거 생성 후 이벤트 세션을 중지하거나 상태 변경을 하였을 경우 에러로그 파일에 기록되는 것을 확인 할 수 있다.

 

트리거를 사용하는 것은 시스템에 오버헤드를 유발하지만 상황에 따라 필요한 정보를 남겨야 하는 경우 매우 유용하게 사용 할 수 있다. 자신이 관리하는 시스템의 특성을 잘 파악하여 사용 할 수 있도록 하자.

 

 

2013-10-10 / 강성욱 / http://sqlmvp.kr