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 クエリ タブを開き、両方のクエリを同じインスタンスに接続します。 クエリ タブのいずれかで次のスクリプトを実行します。 このスクリプトは、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
最初のクエリ タブで次のスクリプトを実行します。このスクリプトはブロックされ、 Production.Locationのロックの取得を待機しています。 短いタイムアウトの後、SQL Server は、このスクリプトまたはサンプル内のスクリプトをデッドロックの対象として選択し、トランザクションを終了します。
SELECT TOP(1) Name FROM Production.Location WITH (XLOCK) ;
GO
デッドロックが発生した後、SQL Server エージェントがアラートをアクティブ化してジョブを実行するまでしばらく待ちます。 次のスクリプトを実行して、 DeadlockEvents テーブルの内容を確認します。
SELECT * FROM DeadlockEvents ;
GO
DeadlockGraph列には、デッドロック グラフ イベントのすべてのプロパティを示す XML ドキュメントが含まれている必要があります。