Exemplo: Criar um alerta do SQL Server Agent com o provedor WMI

Aplica-se a:SQL Server

Uma forma comum de usar o WMI Event Provider é criar alertas SQL Server Agent que respondam a eventos específicos. O exemplo seguinte apresenta um alerta simples que guarda eventos de grafo de deadlock XML numa tabela para análise posterior. O SQL Server Agent submete um pedido WQL, recebe eventos WMI e executa um trabalho em resposta ao evento. Note que, embora vários objetos Service Broker estejam envolvidos no processamento da mensagem de notificação, o Provedor de Eventos WMI trata dos detalhes da criação e gestão destes objetos.

Example

Primeiro, é criada uma tabela na AdventureWorks2022 base de dados para conter o evento do grafo deadlock. A tabela contém duas colunas: a AlertTime coluna contém o tempo em que o alerta é executado, e a DeadlockGraph coluna contém o documento XML que contém o gráfico de bloqueio.

Depois, o alerta é criado. O script cria primeiro o job que o alerta irá executar, adiciona um passo de job ao job e direciona o job para a instância atual do SQL Server. O script cria então o alerta.

O passo do trabalho recupera a TextData propriedade da instância do evento WMI e insere esse valor na DeadlockGraph coluna da DeadlockEvents tabela. O SQL Server converte implicitamente a string para formato XML. Como o passo do trabalho utiliza o subsistema Transact-SQL, o passo do trabalho não especifica um proxy.

O alerta executa o trabalho sempre que um evento de traço de grafo deadlock for registado. Para um alerta WMI, o SQL Server Agent cria uma consulta de notificação usando o namespace e a instrução WQL especificados. Para este alerta, o SQL Server Agent monitoriza a instância padrão no computador local. A instrução WQL solicita qualquer DEADLOCK_GRAPH evento na instância predefinida. Para alterar a instância que o alerta monitoriza, substitua-se o nome da instância por MSSQLSERVER em @wmi_namespace para o alerta.

USE AdventureWorks2022;
GO

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

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

Adiciona uma tarefa para o alerta correr.

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

Adiciona um passo de trabalho que insere o tempo atual e o gráfico de deadlock na DeadlockEvents tabela.

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

Defina o servidor de tarefas para a instância atual do SQL Server.

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

Adicione um alerta que responda a todos DEADLOCK_GRAPH os eventos da instância padrão. Para monitorizar deadlocks de uma instância diferente, mude MSSQLSERVER para o nome da instância.

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

Testar a amostra

Ver o trabalho a funcionar, provocar um impasse. No SQL Server Management Studio, abra dois separadores de SQL Query e ligue ambas as consultas à mesma instância. Execute o seguinte script numa das abas de consulta. Este script produz um conjunto de resultados e termina.

USE AdventureWorks2022;
GO

BEGIN TRANSACTION;
GO

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

Execute o seguinte script no segundo separador de consulta. Este script produz um conjunto de resultados e depois bloqueia, à espera de adquirir um bloqueio em 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

Execute o script seguinte no primeiro separador de consulta. Este script bloqueia, à espera de adquirir um bloqueio em Production.Location. Após um curto intervalo, o SQL Server escolhe este script ou o script do exemplo como vítima do deadlock e termina a transação.

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

Depois de provocar o bloqueio, espere alguns momentos 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 mostre todas as propriedades do evento do grafo deadlock.

Conteúdo relacionado

• Fornecedor WMI para conceitos de Eventos de Servidor