Exemplo: Criação de um alerta do SQL Server Agent usando o provedor WMI para eventos do servidor
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 AdventureWorks 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. Primeiro, o script cria o trabalho que o alerta irá executar, depois adiciona uma etapa de trabalho ao trabalho e direciona o trabalho para a instância atual de SQL Server. Então, o script cria o alerta.
A etapa de trabalho recupera a propriedade TextData da instância do evento WMI e insere esse valor na coluna DeadlockGraph da tabela DeadlockEvents. Observe que SQL Server converte implicitamente a cadeia de caracteres para o formato XML. Como a etapa de trabalho usa o subsistema Transact-SQL, ela não especifica um proxy.
O alerta executa o trabalho sempre que um evento de rastreamento do gráfico 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.
.gif "Observação") Observação |
|---|
Para que o SQL Server Agent receba eventos WMI, Service Broker deve ser habilitado em msdb e AdventureWorks2012 . |
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 a execução do trabalho, provoque um deadlock. Em SQL Server Management Studio, abra duas guias SQL Query e conecte ambas consultas à mesma instância. Execute o seguinte script em um das guias de consulta. Este script produz um conjunto de resultados e é encerrado.
USE AdventureWorks ;
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 gera um conjunto de resultados e então fica bloqueado, aguardando a aquisição de um bloqueio em 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 seguinte o script na primeira guia de consulta. Este script fica bloqueado, enquanto esperando adquirir um bloqueio em Production.Location. Depois de um tempo limite curto, o SQL Server escolherá este 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.