Cómo encontrar la razón por la que TempDB está lleno en su SQL Server

Si la base de datos temporal DB de su SQL Server se queda sin espacio, puede causar importantes interrupciones en su entorno de producción y puede interrumpir la finalización satisfactoria de las aplicaciones de usuario. Si está utilizando una secuencia de comandos para rastrear el tamaño de la base de datos temporal, agregue la secuencia de comandos de este artículo para identificar la causa raíz del llenado de la base de datos temporal.

Tempdb full: un escenario común

Las consultas mal escritas pueden crear varios objetos temporales, lo que resulta en una base de datos tempdb cada vez más grande. Esto terminará en alertas de espacio en disco y podría causar problemas en el servidor. Cuando hay muchos SQL Server A los administradores de bases de datos les resulta muy difícil reducir el tamaño de la base de datos tempdb, por lo que optan inmediatamente por reiniciar el servidor. Si se han intentado todos los métodos para reducir el tamaño de la base de datos tempdb y aún así no se reduce, la última opción es reiniciar el servicio SQL a través del administrador de configuración. De esta forma, cesarán las alertas de espacio en disco y también se solucionarán los problemas del servidor. Sin embargo, reiniciar tempdb podría no estar disponible si el problema se produjo en un servidor de producción.

DBCC

En tales casos, hay varios comandos DBCC que, cuando se ejecutan, le permitirían reducir tempdb. Si había configurado scripts para monitorear proactivamente el tamaño de tempdb, puede usar este script para averiguar los rellenos de tempdb. A partir de ahora, este script se ejecutará en todos los servidores vinculados. Puede controlarlo fácilmente poniendo una cláusula where. Puede hacer que el script se ejecute solo cuando haya un problema con su tempdb. La tabla @Tserver se utiliza para almacenar todos los nombres de sus servidores vinculados. Para una base de datos tempdb, Corrupción SQL cambiaría el estado de la base de datos como SOSPECHOSO y esto interrumpirá SQL Server servicio desde el principio.

DECLARE @Tserver TABLE 
  ( 
     cserver VARCHAR(200) 
  ) 

INSERT INTO @Tserver 
VALUES      ('SERVERNAME') 

DECLARE @LogTable TABLE 
  ( 
     cservername VARCHAR(200), 
     cssionid    SMALLINT, 
     callocmb    BIGINT, 
     cdeallocmb  BIGINT, 
     ctext       VARCHAR(4000), 
     cstatement  VARCHAR(4000) 
  ) 
DECLARE c1 CURSOR FOR 
  SELECT * 
  FROM   @Tserver 
DECLARE @cmd    NVARCHAR(4000), 
        @server VARCHAR(200) 

OPEN c1 

FETCH next FROM c1 INTO @server 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      SET @cmd = 'EXEC(''use tempdb Declare @Table1 table  ( cdeallopages bigint, callopages bigint, cssionid smallint, creqstid int ) insert into @Table1 SELECT SUM(internal_objects_dealloc_page_count), SUM(internal_objects_alloc_page_count), session_id, request_id  FROM sys.dm_db_task_space_usage WITH (NOLOCK) WHERE session_id <> @@SPID GROUP BY session_id, request_id declare @Table2 table ( cssionid smallint, callocmb bigint, cdeallocmb bigint, ctext varchar(4000), cstatement varchar(4000) ) insert into @Table2 SELECT TBL1.cssionid, TBL1.callopages * 1.0 / 128 , TBL1.cdeallopages * 1.0 / 128 , TBL3.text, ISNULL( NULLIF( SUBSTRING( TBL3.text,  TBL2.statement_start_offset / 2,  CASE WHEN TBL2.statement_end_offset < TBL2.statement_start_offset  THEN 0  ELSE( TBL2.statement_end_offset - TBL2.statement_start_offset ) / 2 END ), '''''''' ), TBL3.text ) FROM @Table1 AS TBL1 INNER JOIN sys.dm_exec_requests TBL2 WITH (NOLOCK) ON  TBL1.cssionid = TBL2.session_id AND TBL1.creqstid = TBL2.request_id OUTER APPLY sys.dm_exec_sql_text(TBL2.sql_handle) AS TBL3 OUTER APPLY sys.dm_exec_query_plan(TBL2.plan_handle) AS TBL4 WHERE TBL3.text IS NOT NULL OR TBL4.query_plan IS NOT NULL ORDER BY 3 DESC; Select * from @Table2'') at [' + @server + ']' 

      PRINT @cmd 

      INSERT INTO @LogTable 
                  (cssionid, 
                   callocmb, 
                   cdeallocmb, 
                   ctext, 
                   cstatement) 
      EXEC(@cmd) 

      UPDATE @LogTable 
      SET    cservername = @server 
      WHERE  cservername IS NULL 

      FETCH next FROM c1 INTO @server 
  END 

CLOSE c1 

DEALLOCATE c1 

SELECT * 
FROM   @LogTable

Introducción del autor:

Neil Varley es un experto en recuperación de datos en DataNumen, Inc., que es el líder mundial en tecnologías de recuperación de datos, incluyendo recuperar Outlook y productos de software de recuperación de Excel. Para más información visite www.datanumen.com