サンプル: WMI プロバイダーを使用した SQL Server エージェント警告の作成

適用対象: SQL Server

WMI イベント プロバイダーの標準的な使い方の 1 つは、特定のイベントに応答する SQL Server エージェントを作成することです。 次のサンプルでは、後で分析するために、テーブルに XML デッドロック グラフ イベントを保存する簡単な警告を提供しています。 SQL Server エージェントは、WQL 要求の送信、WMI イベントの受信、およびイベントに応答したジョブの実行を行います。 通知メッセージの処理に関連する Service Broker オブジェクトはいくつかありますが、WMI イベント プロバイダーはこれらのオブジェクトの作成および管理の詳細を処理します。

例

まず、デッドロック グラフ イベントを格納するために、AdventureWorks2022 データベースにテーブルが作成されます。 このテーブルには 2 つの列があります。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 で、2 つの SQL クエリ タブを開き、両方のクエリを同じインスタンスに接続します。 次のスクリプトを 2 つのクエリ タブのうちの 1 つで実行します。 このスクリプトは、1 つの結果セットを作成して終了します。

USE AdventureWorks2022;
GO

BEGIN TRANSACTION;
GO

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

2 番目のクエリ タブで次のスクリプトを実行します。このスクリプトは、1 つの結果セットを生成してからブロックし、 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 Provider for Server Events の概念