La tempdb es una de esas cosas dentro de las instancias SQL Server que puede generarte dolores de cabeza y que pueden llegar a ser difíciles de solucionar si no tenemos los medios necesarios.
Los problemas de espacio en la tempdb son algo muy común en las instancias SQL Server, ya que muchas veces estos archivos pueden llegar a crecer hasta llenar el disco, haciendo que la instancia deje de funcionar. A esto hay que añadirle que descubrir qué ha provocado esto es bastante difícil si no se «caza» en el momento o si no se tiene algún recurso para capturar estos eventos, y de esto es de lo que vamos a hablar hoy.
Saber qué procesos que han hecho crecer la tempdb
Como mencioné, saber qué hace crecer la tempdb es una tarea complicada ya que muchas veces no podemos estar presentes en el momento en el que ocurre. Por ello, mi solución de hoy consiste en crear un evento extendido en SQL Server que capture cuando la tempdb crece y qué proceso lo provoca.
Esto es algo que podéis hacer mediante el asistente de creación, pero aquí os dejo los scripts necesarios para crearlo directamente.
CREATE EVENT SESSION [tempdb Database_Growth_Watchdog] ON SERVER ADD EVENT sqlserver.database_file_size_change ( ACTION ( sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.session_nt_username, sqlserver.sql_text ) WHERE ( [database_id] = ( 2 ) ) -- We filter on database_id=2 to get TempDB growth only ) ADD TARGET package0.event_file ( SET filename = 'RUTA DONDE CREAR LOS ARCHIVOS\tempdb_Database_Growth_Watchdog*.xel', max_file_size = ( 10 ) ) WITH ( MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 1 SECONDS, MAX_EVENT_SIZE = 0 KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = ON ) GO ALTER EVENT SESSION [tempdb Database_Growth_Watchdog] ON SERVER STATE = START
Este script creará el evento. Tendréis que sustituir «RUTA DONDE CREAR LOS ARCHIVOS» por la ruta donde se crearán los archivos.
USE BASEDEDATOS
GO
CREATE PROCEDURE dbo.xsp_tempdbGrowth
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TraceFileLocation nvarchar(255) = N'RUTA DONDE CREAR LOS ARCHIVOS\tempdb_Database_Growth_Watchdog*.xel';
WITH FileSizeChangedEvent AS (
SELECT
object_name AS Event,
CONVERT (xml, event_data) AS Data
FROM
sys.fn_xe_file_target_read_file (@TraceFileLocation, NULL, NULL, NULL)
)
SELECT
FileSizeChangedEvent.Data.value ('(/event/@timestamp)[1]', 'DATETIME') AS EventTime,
FileSizeChangedEvent.Data.value ('(/event/data/value)[7]', 'BIGINT') AS GrowthInKB,
FileSizeChangedEvent.Data.value ('(/event/action/value)[2]', 'VARCHAR(MAX)') AS ClientUsername,
FileSizeChangedEvent.Data.value ('(/event/action/value)[4]', 'VARCHAR(MAX)') AS ClientHostname,
FileSizeChangedEvent.Data.value ('(/event/action/value)[5]', 'VARCHAR(MAX)') AS ClientAppName,
FileSizeChangedEvent.Data.value ('(/event/action/value)[3]', 'VARCHAR(MAX)') AS ClientAppDBName,
FileSizeChangedEvent.Data.value ('(/event/action/value)[1]', 'VARCHAR(MAX)') AS SQLCommandText,
FileSizeChangedEvent.Data.value ('(/event/data/value)[1]', 'BIGINT') AS SystemDuration,
FileSizeChangedEvent.Data.value ('(/event/data/value)[2]', 'BIGINT') AS SystemDatabaseId,
FileSizeChangedEvent.Data.value ('(/event/data/value)[8]', 'VARCHAR(MAX)') AS SystemDatabaseFileName,
FileSizeChangedEvent.Data.value ('(/event/data/text)[1]', 'VARCHAR(MAX)') AS SystemDatabaseFileType,
FileSizeChangedEvent.Data.value ('(/event/data/value)[5]', 'VARCHAR(MAX)') AS SystemIsAutomaticGrowth,
FileSizeChangedEvent.Data
FROM
FileSizeChangedEvent;
END
Este script creará un procedimiento almacenado que nos permitirá consultar los eventos de este evento (también podremos hacerlo a través de la pestaña de eventos en el SSMS). Tendréis que sustituir «BASEDEDATOS» por el nombre de la base de datos que vais a usar para almacenar este procedimiento almacenado y usar la misma ruta donde se crearon los archivos en el script anterior.
EXEC BASEDEDATOS.dbo.xsp_tempdbGrowth;
Esta consulta nos permitirá ejecutar el procedimiento almacenado para consultar los eventos de crecimiento de la tempdb.
Y ya está, esto creará lo necesario para recoger los datos del proceso que provoque que el tamaño de la tempdb cambie. Obviamente, esto solo empezará a funcionar a partir de su creación, por lo que solo podremos ver los eventos que ocurran después de configurarlo.
Espero que esto os haya resultado de utilidad.