Beispiel: Erstellen einer SQL Server-Agent-Warnung mithilfe des WMI-Anbieters für Serverereignisse

Eine gängige Möglichkeit zum Verwenden des WMI-Ereignisanbieters besteht darin, SQL Server-Agent-Warnungen zu erstellen, die auf bestimmte Ereignisse reagieren. Im folgenden Beispiel wird eine einfache Warnung dargestellt, die XML-Deadlock-Diagrammereignisse in einer Tabelle zur späteren Analyse speichert. DER SQL Server-Agent sendet eine WQL-Anforderung, empfängt WMI-Ereignisse und führt einen Auftrag als Reaktion auf das Ereignis aus. Beachten Sie, dass der WMI-Ereignisanbieter die Details zum Erstellen und Verwalten dieser Objekte verarbeitet, obwohl mehrere Service Broker-Objekte an der Verarbeitung der Benachrichtigung beteiligt sind.

Beispiel

Zunächst wird in der AdventureWorks Datenbank eine Tabelle erstellt, die das Deadlock-Diagrammereignis enthält. Die Tabelle enthält zwei Spalten: Die AlertTime Spalte enthält die Zeit, zu der die Warnung ausgeführt wird, und die DeadlockGraph Spalte enthält das XML-Dokument, das das Deadlock-Diagramm enthält.

Anschließend wird die Warnung erstellt. Das Skript erstellt zuerst den Auftrag, den die Warnung ausführt, fügt dem Auftrag einen Auftragsschritt hinzu und zielt auf den Auftrag auf die aktuelle Instanz von SQL Server ab. Das Skript erstellt dann die Warnung.

Der Auftragsschritt ruft die TextData-Eigenschaft der WMI-Ereignisinstanz ab und fügt diesen Wert in die DeadlockGraph-Spalte der DeadlockEvents-Tabelle ein. Beachten Sie, dass SQL Server die Zeichenfolge implizit in das XML-Format konvertiert. Da der Auftragsschritt das Transact-SQL-Subsystem verwendet, gibt der Auftragsschritt keinen Proxy an.

Die Warnung führt den Auftrag aus, wenn ein Deadlock-Diagrammablaufverfolgungsereignis protokolliert wird. Bei einer WMI-Warnung erstellt der SQL Server-Agent eine Benachrichtigungsabfrage mithilfe des angegebenen Namespaces und der angegebenen WQL-Anweisung. Für diese Warnung überwacht der SQL Server-Agent die Standardinstanz auf dem lokalen Computer. Die WQL-Anweisung fordert jedes DEADLOCK_GRAPH Ereignis in der Standardinstanz an. Wenn Sie die Instanz ändern möchten, die von der Warnung überwacht wird, ersetzen Sie den Namen der Instanz MSSQLSERVER für die @wmi_namespace Warnung.

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  

Testen des Beispiels

Um die Ausführung des Auftrags zu sehen, provozieren Sie einen Deadlock. Öffnen Sie in SQL Server Management Studio zwei SQL-Abfrageregisterkarten , und verbinden Sie beide Abfragen mit derselben Instanz. Führen Sie das folgende Skript auf einer der Abfrageregisterkarten aus. Dieses Skript erzeugt ein Resultset und endet.

USE AdventureWorks ;  
GO  
  
BEGIN TRANSACTION ;  
GO  
  
SELECT TOP(1) Name FROM Production.Product WITH (XLOCK) ;  
GO  

Führen Sie das folgende Skript auf der zweiten Abfrageregisterkarte aus. Dieses Skript erzeugt einen Resultset und blockiert dann, bis eine Sperre angezeigt wird 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  

Führen Sie das folgende Skript auf der ersten Abfrageregisterkarte aus. Dieses Skript blockiert und wartet darauf, eine Sperre Production.Locationzu erhalten. Nach einem kurzen Timeout wählt SQL Server entweder dieses Skript oder das Skript im Beispiel als Deadlock-Opfer aus und beendet die Transaktion.

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

Warten Sie nach dem Aufrufen des Deadlocks mehrere Augenblicke, bis der SQL Server-Agent die Warnung aktiviert und den Auftrag ausführt. Überprüfen Sie den Inhalt der DeadlockEvents Tabelle, indem Sie das folgende Skript ausführen:

SELECT * FROM DeadlockEvents ;  
GO  

Die DeadlockGraph Spalte sollte ein XML-Dokument enthalten, in dem alle Eigenschaften des Deadlock-Diagrammereignisses angezeigt werden.

Siehe auch

Konzepte des WMI-Anbieters für Serverereignisse