Hola, chicos,
¡Buenas tardes!

En este post demostraré cómo obtener información importante sobre diversos eventos que ocurren en la instancia de SQL Server sin tener que activar ningún control o auditoría, simplemente leyendo la información ya recopilada por el rastreo estándar de SQL Server.

Aunque ya he hablado de esto en algunos otros posts, decidí cerrar este tema y hacer un post con varios ejemplos más de uso.

El seguimiento SQL estándar recopila 34 tipos diferentes de eventos, que pueden generarse manualmente mediante herramientas como SQL Server Profiler o con consultas Transact-SQL.

Los siguientes ejemplos muestran cómo obtener información de diferentes tipos de eventos, como Reducción, Crecimiento automático, Actualización automática de estadísticas, Copia de seguridad/Restauración, DBCC, Estadísticas de columnas faltantes, entre otros.

Un punto importante a tener en cuenta es que este seguimiento estándar de SQL Server se ejecuta en segundo plano de forma predeterminada, recopila solo unos pocos eventos y, por lo tanto, genera un impacto mínimo en la instancia de SQL Server.

Al activar un seguimiento utilizando SQL Server Profiler, que muestra resultados en tiempo real a través de la interfaz, puedes terminar generando impactos como degradación del rendimiento en el entorno, especialmente si no filtras muy bien solo los eventos que quieres observar y qué criterios se definirán para identificar las sesiones que formarán parte del seguimiento.

Seguimiento predeterminado de SQL Server

Normalmente, solo tenemos el seguimiento estándar ejecutándose en nuestra instancia de SQL Server, que está habilitado de forma predeterminada, aunque en algunos entornos puede encontrar más de un seguimiento ejecutándose, si un DBA crea un seguimiento personalizado.

Listado de seguimientos activos en la instancia

Para enumerar los seguimientos activos en la instancia, puede utilizar la siguiente consulta:

SELECT * FROM sys.traces

SQL Server - Traces
Servidor SQL: seguimientos

Identificar el seguimiento predeterminado

Aunque el seguimiento predeterminado suele ser el seguimiento con ID = 1, no siempre es así. Por lo tanto, recomiendo utilizar la siguiente consulta para identificar el seguimiento predeterminado:

SELECT * FROM sys.traces WHERE is_default = 1

Listado de eventos de seguimiento estándar

Como se mencionó anteriormente, el seguimiento estándar de SQL Server recopila 34 eventos diferentes. Para identificar la lista completa de eventos, simplemente ejecute esta consulta:

DECLARE @id INT = ( SELECT id FROM sys.traces WHERE is_default = 1 )

SELECT DISTINCT
    eventid,
    name
FROM
    fn_trace_geteventinfo(@id) EI
    JOIN sys.trace_events TE ON EI.eventid = TE.trace_event_id

SQL Server - fn_trace_gettable EventClass
SQL Server - clase de evento fn_trace_gettable

Activación de Standard Trace (ya habilitado después de la instalación)

Para activar el seguimiento predeterminado de SQL Server, es muy simple, simplemente ejecute sp_configure:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'default trace enabled', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

Deshabilitar el seguimiento estándar

Deshabilitar el seguimiento predeterminado de SQL Server es tan simple como activarlo. Simplemente ejecute sp_configure:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'default trace enabled', 0;
GO
RECONFIGURE;
GO
EXEC sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

Identificando los eventos

Una forma sencilla de identificar los eventos que ocurrieron en la instancia es ejecutando la siguiente consulta:

DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1)

SELECT
    B.trace_event_id,
    B.name AS EventName,
    A.DatabaseName,
    A.ApplicationName,
    A.LoginName,
    COUNT(*) AS Quantity
FROM
    sys.fn_trace_gettable(@path, DEFAULT) A
    INNER JOIN sys.trace_events B ON A.EventClass = B.trace_event_id
GROUP BY
    B.trace_event_id,
    B.name,
    A.DatabaseName,
    A.ApplicationName,
    A.LoginName
ORDER BY
    B.name,
    A.DatabaseName,
    A.ApplicationName,
    A.LoginName

SQL Server - Trace Events Ocurred
SQL Server: eventos de seguimiento ocurridos

Identificación de eventos de crecimiento automático

Uno de los eventos más importantes que podemos observar con el seguimiento estándar son los eventos de Autocrecimiento, que ocurren cuando esta opción está habilitada en la base de datos y alcanza su tamaño máximo, requiriendo que se asigne más espacio en el disco.

DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1)

SELECT
    DatabaseName,
    [FileName],
    CASE EventClass
      WHEN 92 THEN 'Data File Auto Grow'
      WHEN 93 THEN 'Log File Auto Grow'
    END AS EventClass,
    Duration,
    StartTime,
    EndTime,
    SPID,
    ApplicationName,
    LoginName
FROM
    sys.fn_trace_gettable(@path, DEFAULT)
WHERE
    EventClass IN ( 92, 93 )
ORDER BY
    StartTime DESC

SQL Server - Traces Autogrowth
SQL Server: rastrea el crecimiento automático

Identificación de eventos de reducción de disco

Otro evento realmente interesante para monitorear es el evento Disk Shrink, que ocurre cuando la base de datos asigna más espacio del que usa y el DBA reasigna el espacio asignado, lo que genera espacio libre en el sistema de archivos.

DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1)

SELECT
    TextData,
    Duration,
    StartTime,
    EndTime,
    SPID,
    ApplicationName,
    LoginName
FROM
    sys.fn_trace_gettable(@path, DEFAULT)
WHERE
    EventClass IN ( 116 ) AND TextData LIKE 'DBCC%SHRINK%'
ORDER BY
    StartTime DESC

SQL Server - Traces Shrink
SQL Server: reducción de seguimientos

Identificar cuándo se ejecutaron los comandos DBCC en la instancia

Auditoría muy útil para identificar quién ejecutó o cuándo se ejecutó un comando DBCC en la instancia.

DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1)

SELECT
    TextData,
    Duration,
    StartTime,
    EndTime,
    SPID,
    ApplicationName,
    LoginName
FROM
    sys.fn_trace_gettable(@path, DEFAULT)
WHERE
    EventClass IN ( 116 )
ORDER BY
    StartTime DESC

SQL Server - Trace DBCC
Servidor SQL: seguimiento de DBCC

Identificar cuándo se realizaron las copias de seguridad

Otra auditoría muy interesante para el DBA, que permite identificar la fecha y quién ejecutó las copias de seguridad en la instancia (que también se puede consultar en la tabla msdb.dbo.backupset).

DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1)

SELECT
    DatabaseName,
    TextData,
    Duration,
    StartTime,
    EndTime,
    SPID,
    ApplicationName,
    LoginName
FROM
    sys.fn_trace_gettable(@path, DEFAULT)
WHERE
    EventClass IN ( 115 ) 
    AND EventSubClass = 1
ORDER BY
    StartTime DESC

SQL Server - Trace Backup
SQL Server: copia de seguridad de seguimiento

Identificar cuándo se restauraron las copias de seguridad

Auditoría muy interesante que permite identificar la fecha y qué usuario realizó una restauración en la instancia (que también se puede consultar en las tablas msdb.dbo.restorehistory, msdb.dbo.restorefile y msdb.dbo.restorefilegroup)

DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1)

SELECT
    TextData,
    Duration,
    StartTime,
    EndTime,
    SPID,
    ApplicationName,
    LoginName
FROM
    sys.fn_trace_gettable(@path, DEFAULT)
WHERE
    EventClass IN ( 115 ) 
    AND EventSubClass = 2
ORDER BY
    StartTime DESC

SQL Server - Traces Restore
SQL Server: restauración de seguimientos

Estos son sólo algunos ejemplos de lo que devuelve el seguimiento estándar. ¡Hay una extensa lista de eventos para que explores!
¡Mucha suerte, un abrazo y nos vemos en el próximo post!