Exemplo: criar um alerta do SQL Server Agent com o Provedor WMI

  • Artigo

Aplica-se a:SQL Server

Uma forma comum de usar o Provedor de eventos de WMI é criar alertas do SQL Server Agent que respondem a eventos específicos. O seguinte exemplo apresenta um alerta simples que salva eventos de gráfico 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 de WMI manipula os detalhes da criação e do gerenciamento desses objetos.

Exemplo

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

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

A etapa de trabalho recupera a TextData propriedade da instância de evento WMI e insere esse valor na DeadlockGraph coluna da DeadlockEvents tabela. 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 do grafo deadlock é registrado. Para um alerta de WMI, o SQL Server Agent cria uma consulta de notificação que usa o namespace e a instrução WQL especificados. Para esse alerta, o SQL Server Agent monitora a instância padrão no computador local. A instrução WQL solicita quaisquer eventos DEADLOCK_GRAPH na instância padrão. Para alterar a instância que o alerta monitora, substitua o nome de instância para MSSQLSERVER no @wmi_namespace para o alerta.

Observação

Para que o SQL Server Agent receba eventos WMI, o Service Broker deve estar habilitado em msdb e AdventureWorks2022.

USE AdventureWorks2022;
GO

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

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

Adicione um trabalho para que o alerta seja executado.

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

Adicione uma etapa de trabalho que insere a hora 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 trabalho para o trabalho 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 os DEADLOCK_GRAPH eventos da instância padrão. Para monitorar deadlocks para uma instância diferente, altere 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

O exemplo de teste

Para ver a execução do trabalho, provoque um deadlock. No SQL Server Management Studio, abra duas guias Consulta SQL e conecte ambas as consultas à mesma instância. Execute o seguinte script em um das guias de consulta. Este script produz um conjunto de resultados e é encerrado.

USE AdventureWorks2022;
GO

BEGIN TRANSACTION;
GO

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

Execute o seguinte script na segunda guia de consulta. Este script produz um conjunto de resultados e, em seguida, blocos, aguardando para adquirir um bloqueio no 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 seguinte script na primeira guia de consulta. Este script bloqueia, esperando para adquirir um bloqueio no Production.Location. Após um curto tempo limite, o SQL Server escolherá esse script ou o script no exemplo como a 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 algum tempo para o SQL Server Agent ativar o alerta e executar o trabalho. Examine o conteúdo da tabela DeadlockEvents executando o seguinte script:

SELECT * FROM DeadlockEvents;
GO

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

Conteúdo relacionado