다음을 통해 공유

  • 아티클

예제: 서버 이벤트용 WMI 공급자를 사용하여 SQL Server 에이전트 경고 만들기

WMI 이벤트 공급자를 사용하는 일반적인 방법 중 하나는 특정 이벤트에 응답하는 SQL Server 에이전트 경고를 만드는 것입니다. 다음 예제에서는 XML 교착 상태 그래프 이벤트를 나중에 분석할 수 있도록 테이블에 저장하는 간단한 경고를 보여 줍니다. SQL Server 에이전트는 WQL 요청을 전송하고 WMI 이벤트를 수신하고 이벤트에 대한 응답으로 작업을 실행합니다. 알림 메시지 처리와 관련된 Service Broker 개체가 여러 개 있지만 WMI 이벤트 공급자가 이러한 개체의 생성 및 관리 세부 정보를 처리합니다.

예

먼저 교착 상태 그래프 이벤트를 저장할 AdventureWorks2008R2 데이터베이스에 테이블을 만듭니다. 이 테이블에는 두 개의 열, 즉 경고가 실행되는 시간을 저장하는 AlertTime 열과 교착 상태 그래프가 포함된 XML 문서가 들어 있는 DeadlockGraph 열이 있습니다.

그런 다음 경고를 만듭니다. 스크립트에서는 경고에서 실행할 작업을 만들어 작업에 작업 단계를 추가하고 SQL Server의 현재 인스턴스를 작업의 대상으로 지정합니다. 그런 후 스크립트에서는 경고를 만듭니다.

작업 단계에서는 WMI 이벤트 인스턴스의 TextData 속성을 검색하여 해당 값을 DeadlockEvents 테이블의 DeadlockGraph 열에 삽입합니다. SQL Server에서는 문자열을 XML 형식으로 암시적으로 변환합니다. 작업 단계에서는 Transact-SQL 하위 시스템을 사용하기 때문에 프록시가 지정되지 않습니다.

경고는 교착 상태 그래프 추적 이벤트가 로깅될 때마다 작업을 실행합니다. WMI 경고에 대해 SQL Server 에이전트는 지정된 네임스페이스와 WQL 문을 사용하여 알림 쿼리를 만듭니다. 이 경고에 대해 SQL Server 에이전트는 로컬 컴퓨터에서 기본 인스턴스를 모니터링합니다. WQL 문은 기본 인스턴스에서 모든 DEADLOCK_GRAPH 이벤트를 요청합니다. 경고에서 모니터링하는 대상 인스턴스를 변경하려면 경고의 @wmi_namespace에서 MSSQLSERVER의 인스턴스 이름을 바꿉니다.

[!참고]

SQL Server 에이전트에서 WMI 이벤트를 수신하려면 msdb 및 AdventureWorks2008R2에서 Service Broker를 사용할 수 있어야 합니다.

USE AdventureWorks2008R2 ;
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'AdventureWorks2008R2' ;
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 쿼리 탭 두 개를 열고 두 쿼리를 같은 인스턴스에 연결합니다. 쿼리 탭 중 하나에서 다음 스크립트를 실행합니다. 이 스크립트는 하나의 결과 집합을 생성한 후 종료됩니다.

USE AdventureWorks2008R2 ;
GO

BEGIN TRANSACTION ;
GO

SELECT TOP(1) Name FROM Production.Product WITH (XLOCK) ;
GO

두 번째 쿼리 탭에서 다음 스크립트를 실행합니다. 이 스크립트는 하나의 결과 집합을 생성한 후 Production.Product에 대한 잠금을 얻기 위해 기다리는 동안 차단됩니다.

USE AdventureWorks2008R2 ;
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 문서가 포함되어 있어야 합니다.