Как найти причину, по которой TempDB переполнена в вашем SQL Server

Если временная база данных БД вашего SQL Server не хватает места, это может вызвать серьезные сбои в вашей производственной среде и может помешать успешному завершению пользовательских приложений. Если вы используете сценарий для отслеживания размера временной базы данных, добавьте сценарий из этой статьи, чтобы определить основную причину заполнения временной базы данных.

База данных Tempdb заполнена — распространенный сценарий

Неправильно написанные запросы могут создавать множество временных объектов, что приводит к увеличению размера базы данных tempdb. Это вызовет предупреждения о нехватке дискового пространства и может привести к проблемам с сервером. Когда таких запросов много, SQL Server Администраторам баз данных очень сложно уменьшить размер базы данных tempdb, и они сразу же прибегают к перезапуску сервера. Если были испробованы все методы уменьшения размера базы данных tempdb, и она по-прежнему не уменьшается, последним вариантом остается перезапуск службы SQL через диспетчер конфигурации. Таким образом, прекратятся оповещения о нехватке дискового пространства и проблемы с сервером. Однако перезапуск tempdb может быть недоступен, если проблема возникла на рабочем сервере.

ДБКК

В таких случаях есть несколько команд DBCC, выполнение которых позволит вам сжать базу данных tempdb. Если вы настроили сценарии для упреждающего мониторинга размера базы данных tempdb, вы можете использовать этот сценарий для обнаружения заполнителей базы данных tempdb. На данный момент этот скрипт будет выполняться на всех связанных серверах. Вы можете легко управлять им, поместив предложение where. Вы можете заставить скрипт выполняться только тогда, когда есть проблема с вашей базой данных tempdb. Таблица @Tserver используется для хранения всех имен связанных серверов. Для базы данных tempdb Повреждение SQL изменит статус базы данных на SUSPECT, и это прервет SQL Server обслуживание с момента запуска.

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

Об авторе:

Нил Варли — эксперт по восстановлению данных в DataNumen, Inc., которая является мировым лидером в области технологий восстановления данных, включая восстановить Outlook и программные продукты для восстановления Excel. Для получения дополнительной информации посетите www.datanumen.com