샘플: WMI 공급자를 사용하여 SQL Server 에이전트 경고 만들기

적용 대상:SQL Server

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

예시

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

그런 다음 경고가 생성됩니다. 스크립트는 먼저 경고가 실행되는 작업을 만들고, 작업에 작업 단계를 추가하고, 작업을 SQL Server의 현재 인스턴스로 대상으로 지정합니다. 그런 다음 스크립트가 경고를 만듭니다.

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

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

USE AdventureWorks2022;
GO

IF OBJECT_ID('DeadlockEvents', 'U') IS NOT NULL
DROP TABLE DeadlockEvents;
GO

CREATE TABLE DeadlockEvents (
    AlertTime DATETIME,
    DeadlockGraph XML
);
GO

경고를 실행할 작업을 추가합니다.

EXEC msdb.dbo.sp_add_job @job_name = N'Capture Deadlock Graph',
    @enabled = 1,
    @description = N'Job for responding to DEADLOCK_GRAPH events';
GO

현재 시간 및 교착 상태 그래프를 테이블에 삽입하는 작업 단계를 추가합니다 DeadlockEvents .

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'AdventureWorks2022';
GO

작업의 작업 서버를 SQL Server의 현재 인스턴스로 설정합니다.

EXEC msdb.dbo.sp_add_jobserver @job_name = N'Capture Deadlock Graph';
GO

기본 인스턴스의 모든 DEADLOCK_GRAPH 이벤트에 응답하는 경고를 추가합니다. 다른 인스턴스에 대한 교착 상태를 모니터링하려면 인스턴스 이름으로 변경 MSSQLSERVER 합니다.

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 AdventureWorks2022;
GO

BEGIN TRANSACTION;
GO

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

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

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

관련 콘텐츠

• 서버 이벤트용 WMI 공급자 개념