Voorbeeld: Een SQL Server Agent-waarschuwing maken met de WMI-provider

Van toepassing op:SQL Server

Een veelgebruikte manier om de WMI Event Provider te gebruiken is het maken van SQL Server Agent-waarschuwingen die reageren op specifieke gebeurtenissen. Het volgende voorbeeld presenteert een eenvoudige waarschuwing die XML-deadlock-grafiekgebeurtenissen opslaat in een tabel voor latere analyse. SQL Server Agent dient een WQL-verzoek in, ontvangt WMI-gebeurtenissen en voert een taak uit als reactie op het evenement. Let op dat, hoewel verschillende Service Broker-objecten betrokken zijn bij het verwerken van het meldingsbericht, de WMI Event Provider de details verzorgt van het aanmaken en beheren van deze objecten.

Example

Eerst wordt er een tabel in de AdventureWorks2022 database aangemaakt om het deadlock-graaf-event op te slaan. De tabel bevat twee kolommen: de AlertTime kolom bevat de tijd waarop de waarschuwing wordt uitgevoerd, en de DeadlockGraph kolom bevat het XML-document dat de deadlock-grafiek bevat.

Daarna wordt de melding aangemaakt. Het script maakt eerst de taak aan die de alert zal uitvoeren, voegt een taakstap toe aan de taak en richt de taak op de huidige instantie van SQL Server. Het script maakt vervolgens de melding aan.

De taakstap haalt de TextData eigenschap van de WMI-gebeurtenisinstantie op en voegt die waarde in de DeadlockGraph kolom van de DeadlockEvents tabel in. SQL Server zet de string impliciet om in XML-formaat. Omdat de taakstap het Transact-SQL subsysteem gebruikt, specificeert de taakstap geen proxy.

De waarschuwing voert de taak uit telkens wanneer een deadlock-graaftrace-gebeurtenis wordt geregistreerd. Voor een WMI-waarschuwing maakt SQL Server Agent een notificatiequery aan met behulp van de specificeerde namespace en WQL-instructie. Voor deze waarschuwing houdt SQL Server Agent de standaardinstantie op de lokale computer in de gaten. De WQL-instructie vraagt elk DEADLOCK_GRAPH event op in de standaardinstantie. Om de instantie te veranderen die de alert monitort, vervang je de instantienaam voor MSSQLSERVER in de @wmi_namespace voor de alert.

USE AdventureWorks2022;
GO

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

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

Voeg een taak toe zodat de alert kan worden uitgevoerd.

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

Voeg een taakstap toe die de huidige tijd en de deadlock-grafiek in de DeadlockEvents tabel invoegt.

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

Stel de jobserver voor de taak in op de huidige instantie van SQL Server.

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

Voeg een alert toe die op alle DEADLOCK_GRAPH gebeurtenissen reageert voor de standaardinstantie. Om deadlocks voor een andere instantie te monitoren, verander MSSQLSERVER je de naam van de instantie.

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

Het voorbeeld testen

Om de klus te laten lopen, een patstelling te veroorzaken. Open in SQL Server Management Studio twee SQL Query-tabbladen en verbind beide queries met dezelfde instantie. Voer het volgende script uit in een van de querytabbladen. Dit script produceert één resultaatset en wordt voltooid.

USE AdventureWorks2022;
GO

BEGIN TRANSACTION;
GO

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

Voer het volgende script uit in het tweede querytabblad. Dit script produceert één resultaatset en blokkeert dan, wachtend om een lock op Production.Productte krijgen.

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

Voer het volgende script uit in het eerste querytabblad. Dit script blokkeert, wachtend om een lock op Production.Locationte krijgen. Na een korte time-out kiest SQL Server ofwel dit script of het script in het voorbeeld als deadlock-slachtoffer en beëindigt de transactie.

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

Na het uitlokken van de deadlock, wacht een paar momenten tot SQL Server Agent de alert activeert en de taak uitvoert. Onderzoek de inhoud van de DeadlockEvents tabel door het volgende script uit te voeren:

SELECT * FROM DeadlockEvents;
GO

De DeadlockGraph kolom moet een XML-document bevatten dat alle eigenschappen van het deadlock-graafevent toont.

Verwante inhoud

• WMI-provider voor servergebeurtenisconcepten