예제: 서버 이벤트용 WMI 공급자를 사용하여 SQL Server 에이전트 경고 만들기
WMI 이벤트 공급자를 사용하는 일반적인 방법 중 하나는 특정 이벤트에 응답하는 SQL Server 에이전트 경고를 만드는 것입니다. 다음 예제에서는 XML 교착 상태 그래프 이벤트를 나중에 분석할 수 있도록 테이블에 저장하는 간단한 경고를 보여 줍니다. SQL Server 에이전트는 WQL 요청을 전송하고 WMI 이벤트를 수신하고 이벤트에 대한 응답으로 작업을 실행합니다. 알림 메시지 처리와 관련된 Service Broker 개체가 여러 개 있지만 WMI 이벤트 공급자가 이러한 개체의 생성 및 관리 세부 정보를 처리합니다.
예제
먼저 교착 상태 그래프 이벤트를 저장할 AdventureWorks 데이터베이스에 테이블을 만듭니다. 테이블에는 두 개의 열이 있습니다. 이 열에는 AlertTime 경고가 실행되는 시간이 포함되고 DeadlockGraph 열에는 교착 상태 그래프가 포함된 XML 문서가 포함됩니다.
그런 다음 경고를 만듭니다. 스크립트는 먼저 경고가 실행되는 작업을 만들고, 작업에 작업 단계를 추가하고, SQL Server 현재 instance 작업을 대상으로 합니다. 그런 후 스크립트에서는 경고를 만듭니다.
작업 단계에서는 WMI 이벤트 instance TextData 속성을 검색하고 해당 값을 DeadlockEvents 테이블의 DeadlockGraph 열에 삽입합니다. SQL Server 문자열을 XML 형식으로 암시적으로 변환합니다. 작업 단계에서 Transact-SQL 하위 시스템을 사용하므로 작업 단계에서 프록시를 지정하지 않습니다.
경고는 교착 상태 그래프 추적 이벤트가 로깅될 때마다 작업을 실행합니다. WMI 경고에 대해 SQL Server 에이전트는 지정된 네임스페이스와 WQL 문을 사용하여 알림 쿼리를 만듭니다. 이 경고에 대해 SQL Server 에이전트는 로컬 컴퓨터에서 기본 인스턴스를 모니터링합니다. WQL 문은 기본 인스턴스에서 모든 DEADLOCK_GRAPH 이벤트를 요청합니다. 경고에서 모니터링하는 대상 인스턴스를 변경하려면 경고의 MSSQLSERVER에서 @wmi_namespace의 인스턴스 이름을 바꿉니다.
참고
SQL Server 에이전트 WMI 이벤트를 받으려면 msdb 및 AdventureWorks2012에서 Service Broker를 사용하도록 설정해야 합니다.
USE AdventureWorks ;
GO
IF OBJECT_ID('DeadlockEvents', 'U') IS NOT NULL
BEGIN
DROP TABLE DeadlockEvents ;
END ;
GO
CREATE TABLE DeadlockEvents
(AlertTime DATETIME, DeadlockGraph XML) ;
GO
-- Add a job for the alert to run.
EXEC msdb.dbo.sp_add_job @job_name=N'Capture Deadlock Graph',
@enabled=1,
@description=N'Job for responding to DEADLOCK_GRAPH events' ;
GO
-- Add a jobstep that inserts the current time and the deadlock graph into
-- the DeadlockEvents table.
EXEC msdb.dbo.sp_add_jobstep
@job_name = N'Capture Deadlock Graph',
@step_name=N'Insert graph into LogEvents',
@step_id=1,
@on_success_action=1,
@on_fail_action=2,
@subsystem=N'TSQL',
@command= N'INSERT INTO DeadlockEvents
(AlertTime, DeadlockGraph)
VALUES (getdate(), N''$(ESCAPE_SQUOTE(WMI(TextData)))'')',
@database_name=N'AdventureWorks' ;
GO
-- Set the job server for the job to the current instance of SQL Server.
EXEC msdb.dbo.sp_add_jobserver @job_name = N'Capture Deadlock Graph' ;
GO
-- Add an alert that responds to all DEADLOCK_GRAPH events for
-- the default instance. To monitor deadlocks for a different instance,
-- change MSSQLSERVER to the name of the instance.
EXEC msdb.dbo.sp_add_alert @name=N'Respond to DEADLOCK_GRAPH',
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
@wmi_query=N'SELECT * FROM DEADLOCK_GRAPH',
@job_name='Capture Deadlock Graph' ;
GO
예제 테스트
작업이 실행되는 것을 확인하려면 교착 상태를 발생시켜야 합니다. SQL Server Management Studio 두 개의 SQL 쿼리 탭을 열고 두 쿼리를 동일한 instance 연결합니다. 쿼리 탭 중 하나에서 다음 스크립트를 실행합니다. 이 스크립트는 하나의 결과 집합을 생성한 후 종료됩니다.
USE AdventureWorks ;
GO
BEGIN TRANSACTION ;
GO
SELECT TOP(1) Name FROM Production.Product WITH (XLOCK) ;
GO
두 번째 쿼리 탭에서 다음 스크립트를 실행합니다. 이 스크립트는 하나의 결과 집합을 생성한 다음 에 대한 잠금을 획득하기 위해 대기하여 차단합니다 Production.Product.
USE AdventureWorks ;
GO
BEGIN TRANSACTION ;
GO
SELECT TOP(1) Name FROM Production.Location WITH (XLOCK) ;
GO
SELECT TOP(1) Name FROM Production.Product WITH (XLOCK) ;
GO
첫 번째 쿼리 탭에서 다음 스크립트를 실행합니다. 이 스크립트는 에 대한 잠금을 획득하기 위해 대기하는 를 차단합니다 Production.Location. 짧은 시간 제한 후에 SQL Server 이 스크립트 또는 샘플의 스크립트를 교착 상태 희생자로 선택하고 트랜잭션을 종료합니다.
SELECT TOP(1) Name FROM Production.Location WITH (XLOCK) ;
GO
교착 상태를 발생시킨 후 SQL Server 에이전트가 경고를 활성화하고 작업을 실행할 때까지 잠시 기다립니다. 다음 스크립트를 실행하여 DeadlockEvents 테이블의 내용을 검사합니다.
SELECT * FROM DeadlockEvents ;
GO
DeadlockGraph 열에는 교착 상태 그래프 이벤트의 모든 속성을 보여 주는 XML 문서가 포함되어 있어야 합니다.