TempDB가 가득 찬 이유를 찾는 방법 SQL Server

임시 DB 데이터베이스의 경우 SQL Server 공간이 부족하면 프로덕션 환경에 심각한 장애가 발생할 수 있으며 사용자 애플리케이션이 성공적으로 완료되지 못하도록 방해 할 수 있습니다. 스크립트를 사용하여 임시 DB 크기를 추적하는 경우이 문서의 스크립트를 추가하여 임시 DB 채우기의 근본 원인을 식별하십시오.

Tempdb 가득 참 – 일반적인 시나리오

잘못 작성된 쿼리는 여러 임시 객체를 생성하여 tempdb 데이터베이스의 크기를 증가시킬 수 있습니다. 이로 인해 디스크 공간 부족 경고가 발생하고 서버 문제가 발생할 수 있습니다. SQL Server 데이터베이스 관리자는 tempdb 크기를 줄이는 데 어려움을 겪는 경우가 많아 곧바로 서버 재시작을 선택합니다. tempdb 크기를 줄이기 위해 모든 방법을 시도했는데도 여전히 줄어들지 않는다면, 마지막 선택지는 구성 관리자를 통해 SQL 서비스를 재시작하는 것입니다. 이렇게 하면 디스크 공간 부족 경고가 사라지고 서버 문제도 해결될 수 있습니다. 하지만 문제가 운영 서버에서 발생한 경우에는 tempdb 재시작이 불가능할 수도 있습니다.

DBCC

이러한 경우 실행시 tempdb를 축소 할 수있는 몇 가지 DBCC 명령이 있습니다. 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

저자 소개 :

Neil Varley는 데이터 복구 전문가입니다. DataNumen, Inc.는 다음과 같은 데이터 복구 기술 분야의 세계적 리더입니다. Outlook을 복구 그리고 엑셀 복구 소프트웨어 제품. 자세한 내용은 WWW.datanumen.COM