Olá pessoal,
Tudo bem com vocês ?
Nesse post, eu gostaria de compartilhar com vocês uma forma de criar um monitoramento de erros e exceções no seu banco de dados utilizando Extended Events (XE), permitindo que você consiga capturar e gerar um histórico de erros que ocorrem na sua instância SQL Server, erros estes, que podem ter sido gerado por aplicações, aplicativos Mobile, consultas no Management Studio, serviços Windows, Jobs do SQL Agent, enfim, qualquer que seja a origem dos erros, você poderá capturá-los e tratá-los ou informar para a equipe de desenvolvimento, caso seja responsabilidade deles.
A minha motivação para criar esse tipo de monitoramento era identificar erros de permissões em sistemas e erros de login do usuário da aplicação onde eu trabalho, uma vez que fizemos a alteração da senha desse usuário e precisava ter ferramentas para detectar se algum sistema ou serviço legado (que não possuem logs do lado da aplicação) estavam com erro devido à essa alteração.
Utilizando essa sessão do XE, você poderá capturar praticamente qualquer erro ou exceção que ocorra no seu banco de dados, como por exemplo:
– Erros de permissão em objetos
– Erros de sintaxe
– Objetos inexistentes
– Erros de login
– Erros de rede
– Erros aritméticos e matemáticos
– Warnings do tipo “The definition of object ‘xxx’ has changed since it was compiled.”
– Ocorrência de deadlocks
– Violação de constraints (Check, Foreign Key, Primary Key, Unique Key)
Com isso, com apenas alguns comandos, você poderá ter um relatório bem detalhado dos erros que ocorrem na sua instância, especialmente útil para o time de desenvolvimento analisar erros em aplicações ou o time de DBA analisar erros em jobs do SQL Agent.
Como criar o monitoramento de erros com XE
Para criar o nosso monitoramento de erros, vamos utilizar o recurso Extended Events (XE), do SQL Server. Você pode criá-lo utilizando os comandos abaixo ou utilizando a interface do SSMS, conforme demonstrei no post SQL Server – Como gerar um histórico de deadlocks para análise de falhas em rotinas.
O evento que queremos monitorar é o error_reported.
Passo a passo para criar o XE pela interface do SSMS
-
- Não queremos utilizar um template e sim, montar um novo modelo de acordo com a nossa necessidade.
-
- Selecione o evento que você deseja monitorar. Nesse caso, queremos monitorar o evento “error_reported”
-
- Selecione as colunas que você deseja que sejam retornadas na sessão do XE
-
- Filtros para selecionar os eventos. Aqui não temos opção de filtrar pelo campo severity, e por isso, teremos que criar esse filtro manualmente.
-
- Configurações de onde os eventos serão armazenados no servidor, tamanho máximo, rollout, etc.
-
- Resumo com as configurações da nossa sessão
-
- Aqui é onde configuramos o nome do nosso evento e se ele irá iniciar automaticamente quando a instância foi iniciada
Uma vez que você tenha configurado sua sessão no XE, clique no botão “Script” na última tela ao invés do “Finish”.
Eu te recomendo fazer isso, pois o filtro utilizando a coluna Severity não é possível ser aplicado pela interface do SSMS, apenas editando o script e monitorar alertas com severidade menor que 10 pode acabar gerando um volume de logs muito grande, pois até mesmo alertas e avisos podem acabar sendo monitorados (fica a seu critério).
O script final para criar o monitoramento ficou assim:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- Apaga a sessão, caso ela já exista IF ((SELECT COUNT(*) FROM sys.server_event_sessions WHERE [name] = 'Captura Erros do Sistema') > 0) DROP EVENT SESSION [Captura Errors do Sistema] ON SERVER GO CREATE EVENT SESSION [Captura Errors do Sistema] ON SERVER ADD EVENT sqlserver.error_reported ( ACTION(client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text) -- Adicionado manualmente, pois não é possível filtrar pela coluna "Severity" pela interface WHERE severity > 10 ) ADD TARGET package0.event_file(SET filename=N'C:\Traces\Captura Erros do Sistema',max_file_size=(3),max_rollover_files=(1)) WITH (STARTUP_STATE=ON) -- Será iniciado automaticamente com a instância GO -- Ativando a sessão (por padrão, ela é criada desativada) ALTER EVENT SESSION [Captura Errors do Sistema] ON SERVER STATE = START GO |
Uma importante observação: Se você definir um diretório para guardar os seus eventos do XE, assim como fiz no exemplo (C:\Traces\), você deverá criar esse diretório manualmente. Caso você não faça isso, será gerada uma mensagem de erro ao tentar ativar a sessão, informando que esse diretório não existe.
Como visualizar os erros capturados
Agora que nosso monitoramento foi criado e está ativo, precisamos criar uma rotina para tratar o arquivo do Extended Events e armazenar os resultados em uma tabela.
Dessa forma, fica fácil que outras equipes possam consultar esses dados sem ter que possuir privilégios mais altos para usar as funções do Extended Events, além de facilitar (e muito) a consulta desses erros, uma vez que os dados são gravados no formato XML, e isso exigiria um conhecimento em tratamento de dados no formato XML e acabaria pesando um pouco a instância para buscar e filtrar determinados valores.
Criando a rotina de tratamento dos dados, podemos criar índices nas colunas para facilitar e agilizar a filtragem dos dados, e também criar um histórico maior de erros. No meu caso, limitei o arquivo a possuir apenas 3 MB, mas como faço a cópia dos dados para uma tabela, mesmo que o arquivo seja sobrescrito em decorrências de novos eventos, vou continuar podendo consultar os dados antigos tranquilamente.
Não recomendo criar arquivos gigantes para guardar seus eventos para não ter que criar essa rotina. Quanto maior for seu arquivo XML de resultados do XE, maior será o esforço do banco quando você precisar filtrar alguma informação direto no XML. Mantendo o arquivo pequeno, esse esforço será mínimo.
Código-fonte do script de visualização dos dados
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
IF (OBJECT_ID('dbo.Historico_Erros_Banco') IS NULL) BEGIN -- DROP TABLE dbo.Historico_Erros_Banco CREATE TABLE dbo.Historico_Erros_Banco ( Dt_Evento DATETIME, session_id INT, [database_name] VARCHAR(100), session_nt_username VARCHAR(100), client_hostname VARCHAR(100), client_app_name VARCHAR(100), [error_number] INT, severity INT, [state] INT, sql_text XML, [message] VARCHAR(MAX) ) CREATE CLUSTERED INDEX SK01_Historico_Erros ON dbo.Historico_Erros_Banco(Dt_Evento) END DECLARE @TimeZone INT = DATEDIFF(HOUR, GETUTCDATE(), GETDATE()) DECLARE @Dt_Ultimo_Evento DATETIME = ISNULL((SELECT MAX(Dt_Evento) FROM dbo.Historico_Erros_Banco WITH(NOLOCK)), '1990-01-01') IF (OBJECT_ID('tempdb..#Eventos') IS NOT NULL) DROP TABLE #Eventos ;WITH CTE AS ( SELECT CONVERT(XML, event_data) AS event_data FROM sys.fn_xe_file_target_read_file(N'C:\Traces\Captura Erros do Sistema*.xel', NULL, NULL, NULL) ) SELECT DATEADD(HOUR, @TimeZone, CTE.event_data.value('(//event/@timestamp)[1]', 'datetime')) AS Dt_Evento, CTE.event_data INTO #Eventos FROM CTE WHERE DATEADD(HOUR, @TimeZone, CTE.event_data.value('(//event/@timestamp)[1]', 'datetime')) > @Dt_Ultimo_Evento SET QUOTED_IDENTIFIER ON INSERT INTO dbo.Historico_Erros_Banco SELECT A.Dt_Evento, xed.event_data.value('(action[@name="session_id"]/value)[1]', 'int') AS [session_id], xed.event_data.value('(action[@name="database_name"]/value)[1]', 'varchar(100)') AS [database_name], xed.event_data.value('(action[@name="session_nt_username"]/value)[1]', 'varchar(100)') AS [session_nt_username], xed.event_data.value('(action[@name="client_hostname"]/value)[1]', 'varchar(100)') AS [client_hostname], xed.event_data.value('(action[@name="client_app_name"]/value)[1]', 'varchar(100)') AS [client_app_name], xed.event_data.value('(data[@name="error_number"]/value)[1]', 'int') AS [error_number], xed.event_data.value('(data[@name="severity"]/value)[1]', 'int') AS [severity], xed.event_data.value('(data[@name="state"]/value)[1]', 'int') AS [state], TRY_CAST(xed.event_data.value('(action[@name="sql_text"]/value)[1]', 'varchar(max)') AS XML) AS [sql_text], xed.event_data.value('(data[@name="message"]/value)[1]', 'varchar(max)') AS [message] FROM #Eventos A CROSS APPLY A.event_data.nodes('//event') AS xed (event_data) |
Com esse código, você pode criar uma SP e programar um job do SQL Agent para realizar essa coleta a cada “X” minutos nas suas instâncias. Dessa forma, qualquer usuário com permissão de SELECT nessa tabela pode facilmente consultar os erros da sua instância SQL Server.
E o SQL Server 2008?
Caso você esteja utilizando (AINDA) o SQL Server 2008/2008 R2, você não vai conseguir criar essa sessão do XE com o código acima, pois alguns recursos ainda não haviam sido implementados nessa versão do SQL Server, como os parâmetros client_app_name, sqlserver.client_hostname, sqlserver.database_name. Além disso, ao utilizar a função fn_xe_file_target_read_file no 2008, é necessário informar o caminho do arquivo de metadados também, além do target package0.event_file não ser suportado.
Devido à essas diferenças, vou compartilhar aqui com vocês uma versão dos códigos acima, adaptados para funcionar no SQL Server 2008.
Script de monitoramento:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- Apaga a sessão, caso ela já exista IF ((SELECT COUNT(*) FROM sys.dm_xe_sessions WHERE [name] = 'Captura Erros do Sistema') > 0) DROP EVENT SESSION [Captura Errors do Sistema] ON SERVER GO CREATE EVENT SESSION [Captura Errors do Sistema] ON SERVER ADD EVENT sqlserver.error_reported ( ACTION(sqlserver.client_hostname,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text) -- Adicionado manualmente, pois não é possível filtrar pela coluna "Severity" pela interface WHERE severity > 10 ) ADD TARGET package0.asynchronous_file_target(SET filename=N'C:\Traces\Captura Erros do SQL2008.xel',max_file_size=(3),max_rollover_files=(1)) WITH (STARTUP_STATE=ON) -- Será iniciado automaticamente com a instância GO -- Ativando a sessão (por padrão, ela é criada desativada) ALTER EVENT SESSION [Captura Errors do Sistema] ON SERVER STATE = START GO |
Script da coleta dos dados:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
IF (OBJECT_ID('dbo.Historico_Erros_Banco') IS NULL) BEGIN -- DROP TABLE dbo.Historico_Erros_Banco CREATE TABLE dbo.Historico_Erros_Banco ( Dt_Evento DATETIME, session_id INT, session_nt_username VARCHAR(100), client_hostname VARCHAR(100), [error_number] INT, severity INT, [state] INT, sql_text XML, [message] VARCHAR(MAX) ) CREATE CLUSTERED INDEX SK01_Historico_Erros ON dbo.Historico_Erros_Banco(Dt_Evento) END DECLARE @TimeZone INT = DATEDIFF(HOUR, GETUTCDATE(), GETDATE()) DECLARE @Dt_Ultimo_Evento DATETIME = ISNULL((SELECT MAX(Dt_Evento) FROM dbo.Historico_Erros_Banco WITH(NOLOCK)), '1990-01-01') IF (OBJECT_ID('tempdb..#Eventos') IS NOT NULL) DROP TABLE #Eventos ;WITH CTE AS ( SELECT CONVERT(XML, event_data) AS event_data FROM sys.fn_xe_file_target_read_file(N'C:\Traces\Captura Erros do SQL2008*.xel', 'C:\Traces\Captura Erros do SQL2008*.xem', NULL, NULL) ) SELECT DATEADD(HOUR, @TimeZone, CTE.event_data.value('(//event/@timestamp)[1]', 'datetime')) AS Dt_Evento, CTE.event_data INTO #Eventos FROM CTE WHERE DATEADD(HOUR, @TimeZone, CTE.event_data.value('(//event/@timestamp)[1]', 'datetime')) > @Dt_Ultimo_Evento SET QUOTED_IDENTIFIER ON INSERT INTO dbo.Historico_Erros_Banco SELECT A.Dt_Evento, xed.event_data.value('(action[@name="session_id"]/value)[1]', 'int') AS [session_id], xed.event_data.value('(action[@name="session_nt_username"]/value)[1]', 'varchar(100)') AS [session_nt_username], xed.event_data.value('(action[@name="client_hostname"]/value)[1]', 'varchar(100)') AS [client_hostname], xed.event_data.value('(data[@name="error_number"]/value)[1]', 'int') AS [error_number], xed.event_data.value('(data[@name="severity"]/value)[1]', 'int') AS [severity], xed.event_data.value('(data[@name="state"]/value)[1]', 'int') AS [state], CAST(xed.event_data.value('(action[@name="sql_text"]/value)[1]', 'varchar(max)') AS XML) AS [sql_text], xed.event_data.value('(data[@name="message"]/value)[1]', 'varchar(max)') AS [message] FROM #Eventos A CROSS APPLY A.event_data.nodes('//event') AS xed (event_data) |
And that's it, folks!
Espero que tenham gostado desse post.
Forte abraço!
Portuguese 
English