Exemplo: criando um alerta do SQL Server Agent usando o provedor WMI para eventos de servidor

Uma maneira comum de usar o Provedor de Eventos WMI é criar alertas do SQL Server Agent que respondem a eventos específicos. O exemplo a seguir apresenta um alerta simples que salva eventos de grafo de deadlock XML em uma tabela para análise posterior. O SQL Server Agent envia uma solicitação WQL, recebe eventos WMI e executa um trabalho em resposta ao evento. Observe que, embora vários objetos do Service Broker estejam envolvidos no processamento da mensagem de notificação, o Provedor de Eventos WMI manipula os detalhes da criação e gerenciamento desses objetos.

Exemplo

Primeiro, uma tabela é criada no AdventureWorks banco de dados para manter o evento de grafo deadlock. A tabela contém duas colunas: a AlertTime coluna contém a hora em que o alerta é executado e a DeadlockGraph coluna contém o documento XML que contém o grafo de deadlock.

Em seguida, o alerta é criado. O script primeiro cria o trabalho que o alerta executará, adiciona uma etapa de trabalho ao trabalho e direciona o trabalho para a instância atual do SQL Server. Em seguida, o script cria o alerta.

A etapa de trabalho recupera a propriedade TextData da instância de evento WMI e insere esse valor na coluna DeadlockGraph da tabela DeadlockEvents . Observe que o SQL Server converte implicitamente a cadeia de caracteres em formato XML. Como a etapa de trabalho usa o subsistema Transact-SQL, a etapa de trabalho não especifica um proxy.

O alerta executa o trabalho sempre que um evento de rastreamento de grafo de deadlock é registrado. Para um alerta WMI, o SQL Server Agent cria uma consulta de notificação usando o namespace e a instrução WQL especificadas. Para esse alerta, o SQL Server Agent monitora a instância padrão no computador local. A instrução WQL solicita qualquer DEADLOCK_GRAPH evento na instância padrão. Para alterar a instância que o alerta monitora, substitua o nome MSSQLSERVER da instância no @wmi_namespace 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  

Testando o exemplo

Para ver o trabalho ser executado, provoque um deadlock. No SQL Server Management Studio, abra duas guias de Consulta SQL e conecte ambas as consultas à mesma instância. Execute o script a seguir em uma das guias de consulta. Esse script produz um conjunto de resultados e termina.

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

Execute o script a seguir na segunda guia de consulta. Esse script produz um conjunto de resultados e, em seguida, bloqueia, aguardando para adquirir um bloqueio.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  

Execute o script a seguir na primeira guia de consulta. Esse script bloqueia, aguardando para adquirir um bloqueio.Production.Location Após um curto período de tempo limite, o SQL Server escolherá esse script ou o script no exemplo como vítima de deadlock e encerrará a transação.

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

Depois de provocar o deadlock, aguarde vários momentos para que o SQL Server Agent ative o alerta e execute o trabalho. Examine o conteúdo da DeadlockEvents tabela executando o seguinte script:

SELECT * FROM DeadlockEvents ;  
GO  

A DeadlockGraph coluna deve conter um documento XML que mostra todas as propriedades do evento de grafo deadlock.

Consulte Também

Provedor WMI para conceitos de eventos de servidor