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

使用例

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

USE AdventureWorks2008R2 ;
GO

BEGIN TRANSACTION ;
GO

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

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

次のスクリプトを 1 つ目のクエリ タブで実行します。このスクリプトは処理をブロックし、Production.Location に対するロックの取得を待機します。すぐにタイムアウトになった後、SQL Server は、このスクリプトとサンプル内のスクリプトのどちらかをデッドロックの対象として選択し、トランザクションを終了します。

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

デッドロックが発生した後、SQL Server エージェントが警告を有効化してジョブを実行するまで、しばらく待ちます。次のスクリプトを実行して、DeadlockEvents テーブルの内容を調べます。

SELECT * FROM DeadlockEvents ;
GO

DeadlockGraph 列には、デッドロック グラフ イベントのすべてのプロパティを示した XML ドキュメントが格納されているはずです。

関連項目

概念