SQL Server의 Shrink TempDB 데이터베이스 개요

TempDB에 대한 몇 가지 중요한 사항

• TempDB는 글로벌 리소스 (연결된 모든 사용자가 사용할 수 있음) 시스템 데이터베이스입니다.
• SQL Server는 SQL 서비스가 다시 시작될 때마다 TempDB 데이터베이스를 다시 만듭니다. 다시 시작하는 동안 모델 데이터베이스에서 MDF 및 LDF 사본을 가져옵니다. MDF 및 LDF 파일의 크기와 수는 미리 구성된 크기로 재설정됩니다.
• SQL Server가 TempDB에서 복구를 수행하지 않고 이전 데이터가 손실 됨
• TempDB 데이터베이스는 항상 단순 복구 모델이며 데이터베이스 백업을 수행 할 수 없습니다.
• TempDB는 트랜잭션을 최소한으로 기록하기 때문에 트랜잭션을 롤백 할 수 없습니다.

TempDB 사용 요약

• 일반적으로 중간 테이블을 준비하기 위해 로컬 임시 테이블 (# 명명 규칙) 및 전역 임시 테이블 (## 명명 규칙)을 만듭니다. SQL Server는 TempDB 데이터베이스에 이러한 임시 테이블을 만듭니다.
• SORT_IN_TEMPDB = ON 절을 사용하여 TempDB에서 인덱스를 만들거나 다시 작성할 수 있습니다. SQL Server는 개체가 속한 데이터베이스 대신 TempDB에서 모든 정렬 계산을 수행합니다.
• SQL Server는 COMMITTED SNAPSHOT 읽기 격리 수준에 TempDB를 사용합니다. SQL Server는 각 레코드에 대해 행 버전 관리를 사용합니다. 이전 버전은 행 버전 관리를 추적하기 위해 TempDB에서 추가로 14 바이트를 얻습니다.
• 커서 작업 테이블, 스풀 작업, GROUP BY, ORDER BY, UNION, DBCC CHECKDB와 같은 중간 정렬 작업과 같은 내부 개체, 임시 대형 개체 저장소, 서비스 브로커 이벤트 알림
• 다중 활성 결과 집합 (MultipleActiveResultSets = True 사용)에서 SQL Server는 버전 관리를 사용하고 TempDB에 저장합니다.

당신은이 기사를 통해 갈 수있는 구성, 운영하고, 임시 데이터베이스 SQL Server 시스템 데이터베이스의 제한 과 는 SQL 서버 임시 데이터베이스 데이터베이스를 모니터링하는 방법 임시 데이터베이스 데이터베이스에 대한 자세한 정보를 얻을 수 있습니다.

Shrink TempDB 활동 개요

SQL Server 2016부터 설치 마법사는 CPU 코어를 자동으로 감지하고 TempDB에 필요한 수의 데이터베이스 파일을 만듭니다. 또한 개별 파일의 최대 증가량을 64MB로 설정합니다.

다음 스크린 샷에서 데모 SQL 인스턴스에 대한 TempDB 구성을 볼 수 있습니다.

이 데모에서는 TempDB 데이터베이스에 대한 자동 증가를 비활성화합니다.

• 참고 : 프로덕션 인스턴스에서는이 작업을 수행하지 마십시오. 데모 목적으로 만 테스트 인스턴스에서 수행하고 있습니다.

다음 쿼리를 실행하여 로컬 임시 테이블을 만들고 여기에 데이터를 삽입합니다.

CREATE TABLE #TempTable (col1 char(1000), col2 char(1000))       SET NOCOUNT ON;     DECLARE @i INT = 1          BEGIN TRAN      WHILE @i <= 150000      BEGIN       INSERT INTO #TempTable values ('A','B')       SET @i += 1      END          COMMIT TRAN          DROP TABLE #TempTable

다음과 같은 오류 메시지가 표시됩니다. SQL Server는 TempDB 로그 파일의 크기를 늘릴 수 없으며 트랜잭션을 수행 할 수 없습니다. 자동 증가를 비활성화하고 문제를 해결하기 위해 자동 증가를 활성화했다고 생각할 수 있습니다. 디스크 크기로 확장 된 TempDB 로그 파일과 같은 상황을 고려하고 로그 파일을 확장 할 수있는 디스크 여유 공간이 충분하지 않습니다.

각 데이터 파일에 대해 최대 크기가 20MB 인 데이터 파일에 대해 자동 증가를 활성화 해 보겠습니다. TempDB에 대한 로그 파일 증가가 활성화되어 있고 최대 파일 크기가 없음을 알 수 있습니다.

쿼리를 다시 실행하여 TempDB를 채우고 오류 메시지를 확인하겠습니다. SQL Server 주 파일 그룹에 여유 공간이 없습니다.

이 시점에서 SQL 인스턴스도 새로 고치려고하면 유사한 오류 메시지가 표시됩니다.

TempDB 데이터베이스를 축소하는 다양한 방법

상위 테이블 보고서 별 디스크 사용량을보고 TempDB에서 공간을 사용하는 개체가 무엇인지 확인할 수 있습니다. TempDB를 마우스 오른쪽 버튼으로 클릭하고 보고서-> 표준 보고서-> 상위 테이블 별 디스크 사용량으로 이동합니다.

이 스크린 샷에서 #TempTable이 TempDB의 공간을 사용하고 있음을 알 수 있습니다.

TempDB에있는 기존 개체를 삭제할 수 있으며 공간을 해제해야합니다. 특히 프로덕션 인스턴스에서는 매번 개체를 드롭 할 수 없습니다. 이로 인해 이전 작업을 모두 잃을 수 있습니다.

DBCC SHRINKFILE을 사용하여 TEMPDB 축소

DBCC SHRINKFILE 명령을 사용하여 TempDB의 데이터 또는 로그 파일을 축소 할 수 있습니다. 이 경우 SQL 서비스를 다시 시작할 필요가 없습니다.

DBCC SHRINKFILE(logical_filename, size_in_MB)

다음 쿼리를 실행하여 TempDB에서 개별 파일 크기를 가져옵니다.

SELECT name,     file_id,     type_desc,     size * 8 / 1024 [TempdbSizeInMB] FROM tempdb.sys.database_files ORDER BY type_desc DESC,       file_id;

DBCC SHRINKFILE 명령을 사용하여 TempDev 데이터 파일을 축소 해 보겠습니다.

DBCC SHRINKFILE(tempdev,10)

축소를 수행하고 다음 출력을 얻습니다.

추가로 파일 축소를 시도 할 수 있습니다.

DBCC SHRINKFILE(tempdev,0)

이런 식으로 개별 데이터 또는 로그 파일을 축소해야합니다.

DBCC SHRINKDATABASE 명령을 사용하여 TEMPDB 축소

DBCC SHRINKDATABASE 명령을 사용하여 TempDB 데이터베이스를 축소 할 수도 있습니다. 명령 구문은 다음과 같습니다.

DBCC SHRINKDATABASE(TempDB, ‘target_percentage_of_free_space’);

이 명령을 사용하여 TempDB를 축소하고 10 %의 여유 공간을 남겨 보겠습니다.

DBCC SHRINKDATABASE(tempdb, 10);

데이터베이스 수준 축소를 수행하고 다음 출력을 얻습니다.

tempdb.sys.database_files를 사용하여 데이터베이스의 데이터 및 로그 파일 크기를 확인할 수 있습니다.

ALTER DATABASE 명령을 사용하여 TempDB 크기 조정

Alter 명령을 사용하여 tempdb 파일의 크기를 조정할 수 있습니다. 데이터 및 로그의 초기 크기가 1GB라고 가정하면이 명령을 사용하여 더 낮은 수준으로 설정할 수 있습니다.

다음 명령은 TempDEV 및 TempLog 파일의 초기 크기를 100MB로 조정합니다.

USE master;     GO     ALTER DATABASE tempdb     MODIFY FILE (NAME = tempdev, SIZE=100Mb);     GO     ALTER DATABASE tempdb     MODIFY FILE (NAME = templog, SIZE=100Mb);     GO

이 설정을 적용하려면 SQL 서비스를 다시 시작해야합니다.

SSMS를 사용하여 TempDB 축소

SSMS GUI 방법을 사용하여 TempDB도 축소 할 수 있습니다. TempDB를 마우스 오른쪽 버튼으로 클릭하고 작업으로 이동합니다. 작업 목록에서 축소를 클릭하고 데이터베이스 또는 파일을 선택할 수 있습니다.

데이터베이스 및 파일 옵션은 모두 앞에서 설명한 DBCC SHRINKDATABASE 및 DBCC SHRINKFILE 명령과 유사합니다.

SQL 서비스를 다시 시작하여 TempDB 데이터베이스 축소

TempDB 데이터베이스를 재활용하고 TempDB 데이터 또는 로그 파일 공간으로 인한 디스크 공간 관련 문제를 해결하는 마지막 옵션이어야합니다. 프로덕션 인스턴스에서는 SQL 서비스를 다시 시작하기위한 다운 타임을 찾기가 어렵습니다. 따라서 다른 옵션을 먼저 고려하고 마지막 옵션으로 수행해야합니다.

Tempdb를 축소하려고하는데 공간이 해제되지 않으면 어떻게됩니까?

때때로 TempDB 데이터베이스를 축소하려고하면 명령이 성공한 것으로 표시되지만 데이터베이스에 여유 공간이 표시되지 않습니다. 현재 워크로드에 따라 TempDB의 SQL Server에 필요한 활성 트랜잭션, 버전 관리 또는 개체 때문일 수 있습니다.

Microsoft 기사를 참조 하면 SQL Server가 유휴 모드 또는 단일 사용자 모드에있을 때 TempDB를 축소해야합니다.

다음 방법을 사용하여 TempDB를 축소 할 수 있습니다.

• DBCC DROPCLEANBUFFERS 명령을 실행하여 캐시 된 인덱스 및 데이터 페이지를 플러시합니다.

  CHECKPOINT;             GO             DBCC DROPCLEANBUFFERS;             GO

• DBCC FREEPROCCACHE 명령을 실행하여 절차 캐시를 지 웁니다.

  DBCC FREEPROCCACHE;             GO

이제 이전 방법을 사용하여 데이터베이스를 축소 해보십시오. DBCC SHRINKDATABASE 명령 대신 DBCC SHRINKFILE을 선호해야합니다.

단일 사용자 모드에서 TempDB 축소

TempDB 크기로 인해 SQL 서비스를 시작할 수없는 경우가 있으며 alter database 명령을 사용하여 초기 크기를 재설정해야합니다. alter database 명령을 실행하여 tempdb 데이터 파일의 크기를 조정했지만 실수로 디스크 여유 공간에 따라 실행 불가능한 파일의 초기 크기를 지정했다고 가정합니다. SQL 서비스를 다시 시작하려고했지만 드라이브에 TempDB 파일을 만들 수있는 충분한 여유 공간이 없기 때문에 시작할 수 없습니다.

최소 구성 모드에서 SQL 서비스를 시작하고 TempDB 파일의 크기를 조정해야합니다.

최소 구성 모드를 사용하려면 SQL 서비스가 중지 된 상태 여야합니다.

• 관리자 권한으로 명령 프롬프트 열기

• SQL Server Binary 폴더로 이동합니다. 내 SQL 인스턴스에서 BINN 폴더의 경로는 다음과 같습니다.

  C : \ Program Files \ Microsoft SQL Server \ MSSQL15.SQL2019CTP \ MSSQL \ Binn

  경로가 확실하지 않은 경우 SQL Server 구성 관리자 및 속성 페이지에서 SQL 서비스를 마우스 오른쪽 단추로 클릭하면 이진 경로를 볼 수 있습니다.

  

• 관리 프롬프트에서 다음 명령을 실행합니다 (내 SQL2019CTP 인스턴스의 경우).

  sqlservr.exe -s SQL2019CTP -c -f

  최소 구성 모드에서 SQL Services를 시작하며 경고 메시지로도 볼 수 있습니다.

  

• 다른 명령 프롬프트를 열고 SQLCMD를 사용하여 SQL 서버에 연결합니다.

  

  • 참고 : 현재 한 명의 관리자 만 SQL Server에 연결할 수 있습니다. 연결을 시도하면 다음 오류 메시지가 표시됩니다.

    

  이제 alter database 명령을 실행하여 TempDB의 크기를 조정할 수 있습니다. 각 tempdb 파일에 대해 alter database 명령을 실행해야합니다.

  USE master;                 GO                 ALTER DATABASE tempdb                 MODIFY FILE (NAME = tempdev, SIZE=100Mb);                 GO                 ALTER DATABASE tempdb                 MODIFY FILE (NAME = templog, SIZE=100Mb);                 GO

  

• 최소 구성 모드에서 SQL 서비스를 시작한 관리 명령 프롬프트를 반환하고 CTRL + C를 눌러 종료합니다.

  

• SQL Server 구성 관리자를 사용하여 SQL 서비스를 시작하고 다음 쿼리를 사용하여 변경 사항을 확인합니다.

  SELECT                 name, file_id, type_desc, size * 8 / 1024 [TempdbSizeInMB]                 FROM sys.master_files                 WHERE DB_NAME(database_id) = 'tempdb'                 ORDER BY type_desc DESC, file_id                 GO

  스크린 샷에서 TempDev 및 TempLog 파일의 초기 크기가 100MB임을 알 수 있습니다.