Ejemplo: Crear una alerta del Agente SQL Server mediante el proveedor WMI para eventos de servidor

Una manera común de usar el proveedor de eventos WMI es crear alertas del Agente SQL Server que responden a eventos específicos. En el ejemplo siguiente se presenta una alerta sencilla que guarda eventos de grafo de interbloqueo XML en una tabla para su posterior análisis. El Agente SQL Server envía una solicitud WQL, recibe eventos WMI y ejecuta un trabajo en respuesta al evento. Tenga en cuenta que, aunque varios objetos de Service Broker participan en el procesamiento del mensaje de notificación, el proveedor de eventos WMI controla los detalles de la creación y administración de estos objetos.

Ejemplo

En primer lugar, se crea una tabla en la AdventureWorks base de datos para contener el evento de grafo de interbloqueo. La tabla contiene dos columnas: la AlertTime columna contiene la hora en que se ejecuta la alerta y la DeadlockGraph columna 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 propiedad TextData de la instancia de evento WMI e inserta ese valor en la columna DeadlockGraph de la tabla DeadlockEvents . Observe que 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 especificadas. Para esta alerta, el Agente SQL Server supervisa la instancia predeterminada en el equipo local. La instrucción WQL solicita cualquier DEADLOCK_GRAPH evento en la instancia predeterminada. Para cambiar la instancia de que supervisa la alerta, sustituya el nombre de instancia de MSSQLSERVER en para @wmi_namespace la alerta.

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  

Probar el ejemplo

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

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

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 varios momentos para que el Agente SQL Server active la alerta y ejecute el trabajo. Para examinar el contenido de la DeadlockEvents tabla, ejecute el siguiente script:

SELECT * FROM DeadlockEvents ;  
GO  

La DeadlockGraph columna debe contener un documento XML que muestre todas las propiedades del evento de grafo de interbloqueo.

Véase también

Conceptos del proveedor WMI para eventos de servidor