サンプル : WMI Provider for Server Events の使用による SQL Server エージェント警告の作成

[アーティクル]
06/18/2013

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

使用例

まず、デッドロックグラフイベントを格納するために、AdventureWorks データベースにテーブルが作成されます。このテーブルには 2 つの列があります。AlertTime 列は警告が実行される時間、DeadlockGraph 列はデッドロックグラフが含まれる XML ドキュメントを格納します。

次に警告が作成されます。スクリプトは、まず、警告が実行するジョブを作成し、ジョブステップをジョブに追加し、そのジョブを 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 および 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 で 2 つの SQL クエリ タブを開き、両方のクエリを同じインスタンスに接続します。次のスクリプトを 2 つのクエリタブのうちの 1 つで実行します。このスクリプトは、1 つの結果セットを作成して終了します。

USE AdventureWorks ;
GO

BEGIN TRANSACTION ;
GO

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

次のスクリプトを 2 つ目のクエリタブで実行します。このスクリプトは、1 つの結果セットを生成し、次に処理をブロックして、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

次のスクリプトを 1 つ目のクエリタブで実行します。このスクリプトは処理をブロックし、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 の使用による SQL Server エージェント警告の作成

使用例

サンプルのテスト

関連項目

概念

その他のリソース