SQL Server - Cómo crear un monitoreo de errores y excepciones en su base de datos usando Extended Events (XE)

Hola, chicos,
¿Estás bien?

En este post me gustaría compartir contigo una forma de crear monitoreo de errores y excepciones en tu base de datos usando Extended Events (XE), permitiéndote capturar y generar un historial de errores que ocurren en tu instancia de SQL Server, errores que pueden haber sido generados por aplicaciones, aplicaciones móviles, consultas en Management Studio, servicios de Windows, Jobs del Agente SQL, en fin, cualquiera que sea el origen de los errores, podrás capturarlos y tratarlos o reportarlos al equipo de desarrollo, si es su responsabilidad.

Mi motivación para crear este tipo de monitoreo fue identificar errores de permisos en los sistemas y errores de inicio de sesión del usuario en la aplicación donde trabajo, ya que cambiamos la contraseña de ese usuario y necesitábamos tener herramientas para detectar si algún sistema o servicio heredado (que no tiene registros en el lado de la aplicación) estaba en error debido a este cambio.

Al utilizar esta sesión XE, puede detectar prácticamente cualquier error o excepción que ocurra en su base de datos, como por ejemplo:
– Errores de permiso en objetos.
– Errores de sintaxis
– Objetos inexistentes
– Errores de inicio de sesión
– Errores de red
– Errores aritméticos y matemáticos
– Advertencias como "La definición del objeto 'xxx' ha cambiado desde que se compiló".
– Aparición de puntos muertos
– Violación de restricciones (Cheque, Clave Foránea, Clave Primaria, Clave Única)

Por tanto, con tan solo unos pocos comandos podrás tener un informe muy detallado de los errores que se producen en tu instancia, especialmente útil para el equipo de desarrollo para analizar errores en aplicaciones o el equipo de DBA para analizar errores en trabajos del Agente SQL.

Cómo crear monitoreo de errores con XE

Para crear nuestro monitoreo de errores, usaremos la función Eventos extendidos (XE) de SQL Server. Puedes crearlo usando los siguientes comandos o usando la interfaz SSMS, como lo demostré en la publicación. SQL Server: cómo generar un historial de interbloqueos para analizar fallas en las rutinas.

El evento que queremos monitorear es el error_reportado.

Paso a paso para crear XE a través de la interfaz SSMS

: No queremos utilizar una plantilla, sino crear un nuevo modelo según nuestras necesidades.

: Seleccione el evento que desea monitorear. En este caso, queremos monitorear el evento "error_reported"

: Seleccione las columnas que desea que se devuelvan en la sesión XE

: Filtros para seleccionar eventos. Aquí no tenemos la opción de filtrar por el campo de gravedad, por lo que tendremos que crear este filtro manualmente.

: Configuraciones sobre dónde se almacenarán los eventos en el servidor, tamaño máximo, implementación, etc.

: Resumen con nuestra configuración de sesión

: Aquí es donde configuramos el nombre de nuestro evento y si se iniciará automáticamente cuando se inicie la instancia.

Una vez que haya configurado su sesión XE, haga clic en el botón "Script" en la última pantalla en lugar de "Finalizar".

Recomiendo hacer esto, ya que el filtro que utiliza la columna Gravedad no se puede aplicar a través de la interfaz SSMS, simplemente editar el script y monitorear las alertas con una gravedad inferior a 10 puede terminar generando un volumen muy grande de registros, ya que incluso las alertas y advertencias pueden terminar siendo monitoreadas (depende de usted).

El script final para crear el monitoreo se veía así:

-- 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 Erros do Sistema] ON SERVER 
GO

CREATE EVENT SESSION [Captura Erros 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 Erros do Sistema] ON SERVER STATE = START
GO

Una observación importante: Si define un directorio para almacenar sus eventos XE, como lo hice en el ejemplo (C:\Traces\), debe crear este directorio manualmente. Si no haces esto se generará un mensaje de error al intentar activar la sesión informándote que este directorio no existe.

Cómo ver los errores capturados

Ahora que nuestro monitoreo ha sido creado y está activo, necesitamos crear una rutina para manejar el archivo de Eventos extendidos y almacenar los resultados en una tabla.

De esta manera, es fácil para otros equipos consultar estos datos sin necesidad de tener mayores privilegios para usar las funciones de Eventos Extendidos, además de facilitar mucho (y mucho más) la consulta de estos errores, ya que los datos se registran en formato XML, y esto requeriría conocimientos en el manejo de datos en formato XML y terminaría pesando un poco la instancia a la hora de buscar y filtrar ciertos valores.

Al crear la rutina de procesamiento de datos, podemos crear índices sobre las columnas para facilitar y acelerar el filtrado de datos, y también crear un mayor historial de errores. En mi caso limité el archivo a solo 3 MB, pero como copio los datos a una tabla, aunque por nuevos eventos se sobrescriba el archivo, seguiré pudiendo consultar los datos antiguos con tranquilidad.

No recomiendo crear archivos gigantes para almacenar tus eventos para no tener que crear esta rutina. Cuanto más grande sea su archivo XML de resultados XE, mayor será el esfuerzo del banco cuando necesite filtrar alguna información directamente en el XML. Al mantener el archivo pequeño, este esfuerzo será mínimo.

Código fuente del script de visualización de datos

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)

Con este código, puede crear un SP y programar un trabajo del Agente SQL para realizar esta recopilación cada "X" minutos en sus instancias. De esta manera, cualquier usuario con permisos SELECT en esta tabla puede consultar fácilmente errores en su instancia de SQL Server.

¿Qué pasa con SQL Server 2008?

Si (AÚN) está utilizando SQL Server 2008/2008 R2, no podrá crear esta sesión XE con el código anterior, ya que algunas características aún no se habían implementado en esta versión de SQL Server, como los parámetros client_app_name, sqlserver.client_hostname, sqlserver.database_name. Además, cuando se utiliza la función fn_xe_file_target_read_file en 2008, también es necesario informar la ruta del archivo de metadatos, además de que el paquete de destino0.event_file no es compatible.

Debido a estas diferencias, compartiré aquí con ustedes una versión de los códigos anteriores, adaptada para funcionar en SQL Server 2008.

Guión de seguimiento:

-- 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 Erros do Sistema] ON SERVER 
GO

CREATE EVENT SESSION [Captura Erros 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 Erros do Sistema] ON SERVER STATE = START
GO

Guión de recopilación de datos:

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)

¡Eso es todo, amigos!
Espero que te haya gustado esta publicación.

¡Gran abrazo!

Dirceu Resende

Arquitecto de Bases de Datos y BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.

WhatsApp Telegram Ver mis certificaciones

Comentários (0)

Carregando comentários…