Teilen über

Beispiel: Erstellen einer SQL Server-Agent-Warnung mit dem WMI-Anbieter

  • Artikel

Gilt für: SQL Server

Eine gebräuchliche Möglichkeit zum Verwenden des WMI-Ereignisanbieters ist die Erstellung von SQL Server-Agent-Warnungen, die auf bestimmte Ereignisse antworten. Das folgende Beispiel stellt eine einfache Warnung dar, die XML-Deadlockdiagrammereignisse in einer Tabelle zur späteren Analyse speichert. SQL Server-Agent übermittelt eine WQL-Anforderung, empfängt WMI-Ereignisse und führt als Antwort auf das Ereignis einen Auftrag aus. Beachten Sie, dass der WMI-Ereignisanbieter die Details bei der Erstellung und Verwaltung von Service Broker-Objekten behandelt, obwohl mehrere dieser Objekte an der Verarbeitung der Benachrichtigungsmeldung beteiligt sind.

Beispiel

Zuerst wird in der AdventureWorks2022-Datenbank eine Tabelle erstellt, in der das Deadlockdiagrammereignis gespeichert werden soll. Die Tabelle enthält zwei Spalten: Die AlertTime-Spalte enthält die Uhrzeit, zu der die Warnung ausgeführt wird, und die DeadlockGraph-Spalte enthält das XML-Dokument mit dem Deadlockdiagramm.

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. SQL Server konvertiert die Zeichenfolge implizit in das XML-Format. Da der Auftragsschritt das Transact-SQL-Subsystem verwendet, gibt der Auftragsschritt keinen Proxy an.

Die Warnung führt den Auftrag immer dann aus, wenn ein Deadlockdiagrammablaufverfolgungsereignis protokolliert werden würde. Für eine WMI-Warnung erstellt SQL Server-Agent mittels angegebenem Namespace und WQL-Anweisung eine Abfragebenachrichtigung. Für diese Warnung überwacht SQL Server-Agent die Standardinstanz auf dem lokalen Computer. Die WQL-Anweisung fordert ein beliebiges DEADLOCK_GRAPH-Ereignis in der Standardinstanz an. Zum Ändern der Instanz, das von der Warnung überwacht wird, ersetzen Sie den Instanznamen durch MSSQLSERVER im @wmi_namespace für die Warnung.

Hinweis

Damit SQL Server-Agent WMI-Ereignisse empfangen können, muss der Service Broker in msdb und AdventureWorks2022.

USE AdventureWorks2022;
GO

IF OBJECT_ID('DeadlockEvents', 'U') IS NOT NULL
DROP TABLE DeadlockEvents;
GO

CREATE TABLE DeadlockEvents (
    AlertTime DATETIME,
    DeadlockGraph XML
);
GO

Fügen Sie einen Auftrag hinzu, damit die Warnung ausgeführt werden kann.

EXEC msdb.dbo.sp_add_job @job_name = N'Capture Deadlock Graph',
    @enabled = 1,
    @description = N'Job for responding to DEADLOCK_GRAPH events';
GO

Fügen Sie einen Auftragsschritt hinzu, der die aktuelle Uhrzeit und das Deadlockdiagramm in die DeadlockEvents Tabelle einfügt.

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

Legen Sie den Auftragsserver für den Auftrag auf die aktuelle Instanz von SQL Server fest.

EXEC msdb.dbo.sp_add_jobserver @job_name = N'Capture Deadlock Graph';
GO

Fügen Sie eine Warnung hinzu, die auf alle DEADLOCK_GRAPH Ereignisse für die Standardinstanz reagiert. Um Deadlocks für eine andere Instanz zu überwachen, ändern Sie MSSQLSERVER den Namen der Instanz.

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 zu sehen, dass der Auftrag ausgeführt wird, 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 auf einer der Abfrageregisterkarten das folgende Skript aus. Dieses Skript erzeugt ein Resultset und endet.

USE AdventureWorks2022;
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 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

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 Provozieren des Deadlocks einen Moment, damit SQL Server-Agent die Warnung aktivieren und den Auftrag ausführen kann. Untersuchen Sie den Inhalt der Tabelle DeadlockEvents, indem Sie das folgende Skript ausführen:

SELECT * FROM DeadlockEvents;
GO

Die DeadlockGraph-Spalte sollte ein XML-Dokument enthalten, das alle Eigenschaften des Deadlockdiagrammereignisses enthält.

Zugehöriger Inhalt