Muestra: Creación de una alerta del Agente SQL Server con el proveedor WMI

Se aplica a:SQL Server

Una manera común de utilizar el Proveedor de eventos WMI es crear alertas del Agente SQL Server que respondan a eventos concretos. En el ejemplo siguiente se presenta una alerta simple que guarda los eventos de grafo de interbloqueo de XML en una tabla para el análisis posterior. El Agente SQL Server envía una solicitud WQL, recibe los eventos WMI y ejecuta un trabajo en respuesta al evento. Observe que, aunque varios objetos Service Broker están implicados para procesar el mensaje de notificación, el Proveedor de eventos WMI administra los detalles de creación y administración de estos objetos.

Ejemplo

Primero, se crea una tabla en la base de datos AdventureWorks2022 para contener el evento de grafo de interbloqueo. La tabla contiene dos columnas: la columna AlertTime contiene la hora a la que se ejecuta la alerta y la columna DeadlockGraph contiene el documento XML que contiene el grafo de interbloqueo.

A continuación, se crea la alerta. El script crea primero el trabajo que ejecutará la alerta, agrega un paso de trabajo al trabajo y tiene como destino el trabajo a la instancia actual de SQL Server. A continuación, el script crea la alerta.

El paso de trabajo recupera la TextData propiedad de la instancia de evento WMI e inserta ese valor en la DeadlockGraph columna de la DeadlockEvents tabla. SQL Server convierte implícitamente la cadena en formato XML. Dado que el paso de trabajo usa el subsistema transact-SQL, el paso de trabajo no especifica un proxy.

La alerta ejecuta el trabajo cada vez que se registraría un evento de seguimiento de grafo de interbloqueo. Para una alerta WMI, el Agente SQL Server crea una consulta de notificación mediante el espacio de nombres y la instrucción WQL especificados. Para este alerta, el Agente SQL Server supervisa la instancia predeterminada en el equipo local. La instrucción WQL solicita cualquier evento DEADLOCK_GRAPH en la instancia predeterminada. Para cambiar la instancia que supervisa la alerta, sustituya el nombre de instancia por MSSQLSERVER en el @wmi_namespace para la alerta.

USE AdventureWorks2022;
GO

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

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

Agregue un trabajo para que se ejecute la alerta.

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

Agregue un paso de trabajo que inserte la hora actual y el grafo de interbloqueo en la DeadlockEvents tabla.

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

Establezca el servidor de trabajos para el trabajo en la instancia actual de SQL Server.

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

Agregue una alerta que responda a todos los DEADLOCK_GRAPH eventos de la instancia predeterminada. Para supervisar interbloqueos para una instancia diferente, cambie MSSQLSERVER al nombre de la instancia.

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

Pruebe el ejemplo

Para ver cómo se ejecuta el trabajo, provoque un interbloqueo. En SQL Server Management Studio, abra dos pestañas consulta SQL y conecte ambas consultas a la misma instancia. Ejecute el script siguiente en una de las pestañas de consulta. Este script genera un conjunto de resultados y finaliza.

USE AdventureWorks2022;
GO

BEGIN TRANSACTION;
GO

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

Ejecute el siguiente script en la segunda pestaña de consulta. Este script genera un conjunto de resultados y, a continuación, bloquea y espera a adquirir un bloqueo en 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

Ejecute el siguiente script en la primera pestaña de consulta. Este script bloquea y espera a adquirir un bloqueo en Production.Location. Después de un breve tiempo de espera, SQL Server elegirá este script o el script del ejemplo como víctima del interbloqueo y finalizará la transacción.

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

Después de provocar el interbloqueo, espere unos momentos a que el Agente SQL Server active la alerta y ejecute el trabajo. Examine el contenido de la tabla DeadlockEvents ejecutando el script siguiente:

SELECT * FROM DeadlockEvents;
GO

La columna DeadlockGraph debería contener un documento XML que muestra todas las propiedades del evento de grafo de interbloqueo.

Contenido relacionado

• Conceptos del proveedor WMI para eventos de servidor