¿Alguna vez te has preguntado cómo hacer un seguimiento del crecimiento y la reducción de archivos de base de datos en SQL Server? En un artículo anterior, compartí un método utilizando eventos extendidos que funcionaba bien en SQL Server 2008. Pero, ¿qué pasa si estás utilizando SQL Server 2012 o 2014? ¿Funcionará el mismo método?

Para averiguarlo, primero verifiquemos si la sesión de eventos extendidos está en ejecución en el servidor. Puedes utilizar la siguiente consulta para confirmarlo:

DECLARE @SessionName VARCHAR(64) = 'TrackDBFileChange'
SELECT sn.SessionName
, CASE WHEN ISNULL(es.name,'No') = 'No'
THEN 'NO'
ELSE 'YES'
End as XESessionExists
, CASE WHEN ISNULL(xe.name,'No') = 'No'
THEN 'NO'
ELSE 'YES'
End as XESessionRunning
FROM (Select @SessionName as SessionName) sn
LEFT OUTER JOIN sys.server_event_sessions es
ON sn.SessionName = es.name
LEFT OUTER JOIN sys.dm_xe_sessions xe
ON es.name = xe.name
;

Si la sesión está en ejecución, podemos proceder a validar si está funcionando correctamente ejecutando un script que haga que los archivos crezcan o se reduzcan. Por ejemplo:

SELECT DB_NAME(database_id) AS DBName,name AS FileName,size/124.0 AS size_mb
FROM master.sys.master_files
WHERE database_id in (2,DB_ID('Sandbox2'));

USE [Sandbox2]
GO
DBCC SHRINKFILE (N'Sandbox2' , 1)
GO

USE [Sandbox2]
GO
DBCC SHRINKFILE (N'Sandbox2_log' , 1)
GO

SELECT DB_NAME(database_id) AS DBName,name AS FileName,size/124.0 AS size_mb
FROM master.sys.master_files
WHERE database_id in (2,DB_ID('Sandbox2'));
GO

Después de ejecutar el script, podemos verificar los datos de la sesión de eventos para confirmar si está capturando los cambios en el tamaño de los archivos. Sin embargo, en SQL Server 2012 y versiones posteriores, la sesión de eventos extendidos anterior ya no funciona debido a la obsolescencia. Pero no te preocupes, hay un nuevo evento que lo reemplaza.

Para actualizar la sesión de eventos extendidos para SQL Server 2012 y versiones posteriores, puedes utilizar la siguiente sesión:

CREATE EVENT SESSION [TrackDBFileChange12] ON SERVER 
ADD EVENT sqlserver.database_file_size_change(
ACTION(sqlserver.session_id,sqlserver.database_name,sqlserver.client_hostname,
            sqlserver.sql_text)
)
,ADD EVENT sqlserver.databases_log_growth(
ACTION(sqlserver.session_id,sqlserver.database_name,sqlserver.client_hostname,
            sqlserver.sql_text)
)
ADD TARGET  package0.asynchronous_file_target(
     SET filename='C:XEDBFileSizeChange12.xel',max_file_size = 5,max_rollover_files = 4
         ,metadatafile='C:XEDBFileSizeChange12.xem')
,
ADD TARGET package0.ring_buffer-- Store events in the ring buffer target
(SET max_memory = 4096)
WITH (MAX_MEMORY = 4MB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, TRACK_CAUSALITY = ON, MAX_DISPATCH_LATENCY = 1 SECONDS,startup_state = ON)
GO
ALTER EVENT SESSION TrackDBFileChange12
ON SERVER 
STATE = START;
GO

Con la sesión de eventos actualizada, ahora puedes verificar los datos de la sesión utilizando la siguiente consulta:

SELECT
    event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name
,event_data.value('(event/@timestamp)[1]','varchar(max)') as timestamp
    ,event_data.value('(event/data[@name="size_change_kb"]/value)[1]', 'bigint') AS SizeChangeKb
,event_data.value('(event/data[@name="total_size_kb"]/value)[1]', 'bigint') AS TotalSizeKb
,event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS Duration_ms
,event_data.value('(event/data[@name="is_automatic"]/value)[1]', 'varchar(20)') AS AutoChangeEvent
,event_data.value('(event/action[@name="sql_text"]/value)[1]', 'varchar(max)') AS sql_text
,event_data.value('(event/action[@name="database_name"]/value)[1]', 'varchar(max)') AS DBQueryExecutedFrom
,db_name(event_data.value('(event/data[@name="database_id"]/value)[1]','int')) as AffectedDB
,event_data.value('(event/data[@name="file_name"]/value)[1]','varchar(max)') as AffectedFile
,event_data.value('(event/data[@name="file_type"]/text)[1]','varchar(max)') as FileType
,event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'varchar(max)') AS ClientHost
,event_data.value('(event/action[@name="session_id"]/value)[1]', 'varchar(max)') AS session_id
FROM(   SELECT CAST(event_data AS xml) AS TargetData
            FROM sys.fn_xe_file_target_read_file('C:XEDBFileSizeChange12*.xel',NULL,NULL, NULL)
        
    ) AS evts(event_data)
WHERE event_data.value('(event/@name)[1]', 'varchar(50)') = 'database_file_size_change'
or event_data.value('(event/@name)[1]', 'varchar(50)') = 'databases_log_growth'
ORDER BY AffectedDB,FileType,timestamp asc;

Con la nueva sesión de eventos, tienes acceso a una mejor información sobre los cambios en los archivos de tu base de datos. Esto puede ayudarte a controlar y supervisar mejor tu entorno.

Para obtener más información y ayuda con los eventos extendidos, puedes consultar la documentación oficial de Microsoft.

¡Feliz seguimiento!