SQL Server에서 tempdb 데이터베이스를 축소하는 방법

요약

이 문서에서는 Microsoft SQL Server에서 tempdb 데이터베이스를 축소하는 데 사용할 수 있는 여러 가지 방법을 설명합니다. 이 문서에서 설명하는 방법을 사용하여 tempdb 데이터베이스를 축소하기 전에 다음 사항을 참조하십시오.

  • 데이터베이스를 다시 시작할 때마다 tempdb 크기는 마지막으로 구성된 크기(기본 크기 또는 alter database를 사용하여 마지막으로 설정한 크기)로 다시 설정됩니다. 따라서 다른 값을 사용해야 하거나 데이터베이스 크기를 즉시 줄여야 하는 경우가 아니라면 이 문서에서 설명하는 절차를 사용할 필요가 없습니다. SQL Server 서비스가 다음번에 다시 시작될 때까지 기다리면 데이터베이스 크기는 자동으로 줄어들기 때문입니다. tempdb 데이터베이스 크기가 크더라도 SQL Server의 성능은 저하되지 않습니다.

  • SQL Server 2005 이하 버전의 경우에는 SQL Server 인스턴스를 다시 시작할 때마다 tempdb가 원래 구성되었던 크기로 다시 설정된다는 점만 제외하면 tempdb 데이터베이스를 축소하는 것은 사용자 데이터베이스를 축소하는 것과 동일합니다.

  • tempdb 활동이 진행 중일 때는 tempdb에서 shrink를 실행하는 것이 안전합니다. 그러나 차단, 교착 상태 등의 기타 오류가 발생하여 shrink를 완료할 수 없는 경우도 있습니다. 그러므로 tempdb를 정상적으로 축소하려면 서버가 단일 사용자 모드인 동안이나 모든 tempdb 활동을 중지한 후에 축소를 수행하는 것이 좋습니다.

tempdb 정보

tempdb 데이터베이스는 임시 작업 영역입니다. SQL Server는 tempdb를 사용하여 다음과 같은 여러 작업을 수행합니다.

  • 명시적으로 만든 임시 테이블 저장

  • 쿼리 처리 및 정렬 중에 생성된 중간 결과를 보관하는 작업 테이블

  • 구체화된 정적 커서

  • 스냅숏 격리 수준 또는 커밋된 읽기 스냅숏 격리 수준 사용 시 버전 레코드 저장

SQL Server는 트랜잭션을 롤백하는 데 충분한 정보만 tempdb 트랜잭션 로그에 기록하며 데이터베이스 복구 중에 트랜잭션을 다시 실행하는 데 필요한 정보는 기록하지 않습니다. 이 기능을 사용하면 tempdb에서 INSERT 문의 성능이 개선됩니다. 또한 tempdb는 SQL Server를 다시 시작할 때마다 다시 작성되므로 트랜잭션을 다시 실행하기 위한 정보는 기록하지 않아도 됩니다. 즉, 롤포워드하거나 롤백할 트랜잭션은 없습니다. SQL Server가 시작되면 tempdb는 model 데이터베이스 복사본을 사용하여 다시 작성되며 마지막으로 구성된 크기로 다시 설정됩니다. 여기서 구성된 크기란 MODIFY FILE 옵션이나 DBCC SHRINKFILE 또는 DBCC SHRINKDATABASE 문을 사용하는 ALTER DATABASE 등의 파일 크기 변경 작업을 통해 마지막으로 설정한 명시적 크기입니다.

tempdb 데이터베이스의 크기는 기본적으로 필요에 따라 자동 증가하도록 구성됩니다. 그러므로 시간이 지남에 따라 이 데이터베이스가 예기치 않게 필요한 크기보다 더 커질 수도 있습니다. 이 경우 SQL Server만 다시 시작하면 tempdb 크기가 마지막으로 구성된 크기로 다시 설정됩니다.

SQL Server 2005 이상 버전에서는 다음 중 원하는 방법을 사용하여 tempdb의 크기를 변경할 수 있습니다.

방법

다시 시작 필요 여부

추가 정보

ALTER DATABASE

필요

기본 tempdb 파일(tempdev 및 templog)의 크기를 완전하게 제어할 수 있습니다.

DBCC SHRINKDATABASE

불필요

데이터베이스 수준에서 작동합니다.

DBCC SHRINKFILE

불필요

개별 파일을 축소할 수 있습니다.

SQL Server Management Studio

불필요

기본적으로 GUI를 통해 데이터베이스 파일을 축소합니다.


참고 축소 작업 후 SQL Server 2005의 SQL Server Management Studio에는 정확한 tempdb 파일 크기가 표시되지 않습니다. "현재 할당된 공간" 값은 항상 sys.master_files DMV에서 가져오며, tempdb 데이터베이스에 대해 축소 작업을 수행한 후에도 이 값은 업데이트되지 않습니다. 축소 작업 후에 정확한 tempdb 파일 크기를 확인하려면 SQL Server Management Studio에서 다음 문을 실행합니다.

use tempdb
go






select (size*8) as FileSizeKB from sys.database_files

여기서는 처음 세 가지 방법에 대해 설명합니다.

참고 SQL Server 2000이 설치되어 있는 경우에는 SQL Server Management Studio 대신 쿼리 분석기를 사용해야 합니다. 또한 DBCC 명령을 실행할 때는 데이터베이스를 단일 사용자 모드로 설정해야 합니다.

다음의 세 가지 방법을 통해 tempdb를 구성된 크기보다 작게 축소할 수 있습니다.

방법 1: Transact-SQL 명령 사용
참고 이 방법을 사용할 때는 SQL Server를 다시 시작해야 합니다.

  1. SQL Server를 중지합니다.

  2. 명령 프롬프트에서 최소 구성 모드로 인스턴스를 시작합니다. 이렇게 하려면 다음 단계를 수행합니다.

    1. 명령 프롬프트에서 다음 폴더로 변경합니다.

    2. 인스턴스가 SQL Server의 명명된 인스턴스인 경우 다음 명령을 실행합니다.

      sqlservr.exe -s InstanceName -c -f 인스턴스가 SQL Server의 기본 인스턴스인 경우 다음 명령을 실행합니다.

      sqlservr -c -f 참고 -c 및 -f 매개 변수를 사용하면 SQL Server가 최소 구성 모드로 시작됩니다. 이 모드의 tempdb 크기는 1MB(데이터 파일용) 및 0.5MB(로그 파일용)입니다.

  3. 쿼리 분석기를 사용하여 SQL Server에 연결한 후 다음 Transact-SQL 명령을 실행합니다.

       ALTER DATABASE tempdb MODIFY FILE
       (NAME = 'tempdev', SIZE = target_size_in_MB) 
       --Desired target size for the data file
    
       ALTER DATABASE tempdb MODIFY FILE
       (NAME = 'templog', SIZE = target_size_in_MB)
       --Desired target size for the log file
    
  4. SQL Server를 중지합니다. 이렇게 하려면 명령 프롬프트 창에서 Ctrl+C를 누르고 SQL Server를 서비스로 다시 시작한 후에 Tempdb.mdf 및 Templog.ldf 파일의 크기를 확인합니다.

이 방법은 tempdb의 기본 논리적 파일인 tempdev와 templog에서만 작동한다는 제한이 있습니다. tempdb에 더 추가하는 파일은 SQL Server를 서비스로 다시 시작한 후에 축소할 수 있습니다. 모든 tempdb 파일은 SQL Server를 시작하는 동안 다시 작성됩니다. 그러나 이러한 파일은 비어 있으므로 제거해도 됩니다. tempdb에서 추가 파일을 제거하려면 REMOVE FILE 옵션을 포함하여 ALTER DATABASE 명령을 사용합니다.

방법 2: DBCC SHRINKDATABASE 명령 사용
DBCC SHRINKDATABASE 명령을 사용하여 tempdb 데이터베이스를 축소할 수 있습니다. DBCC SHRINKDATABASE는 target_percent 매개 변수를 수신합니다. 이 매개 변수는 데이터베이스를 축소한 후 데이터베이스 파일에 남아 있도록 할 사용 가능한 공간 백분율입니다. DBCC SHRINKDATABASE를 사용하는 경우 SQL Server를 다시 시작해야 할 수 있습니다.

  1. sp_spaceused 저장 프로시저를 사용하여 현재 tempdb에서 사용된 공간을 확인합니다. 그런 다음 DBCC SHRINKDATABASE에 대한 매개 변수로 사용할 남은 사용 가능 공간 백분율을 계산합니다. 원하는 데이터베이스 크기를 기준으로 이 계산을 수행합니다.

    참고 sp_spaceused @updateusage=true를 실행하여 사용된 공간을 다시 계산하고 업데이트된 보고서를 생성해야 하는 경우도 있습니다. sp_spaceused 저장 프로시저에 대한 자세한 내용은 SQL Server 온라인 설명서를 참조하십시오.


    다음과 같은 예제를 생각해 볼 수 있습니다.

    tempdb에 두 개의 파일, 즉 크기가 100MB인 기본 데이터 파일(Tempdb.mdf)과 30MB인 로그 파일(Tempdb.ldf)이 있는데 sp_spaceused에서 기본 데이터 파일에 60MB의 데이터가 포함된 것으로 보고한다고 가정해 보겠습니다. 이 기본 데이터 파일을 80MB로 축소하려고 합니다. 이 경우 축소 후에 남아 있도록 할 사용 가능한 공간 백분율은 80MB - 60MB = 20MB입니다. 이 20MB를 80MB로 나누면 25%가 되며, 이 값이 target_percent입니다. 이 매개 변수의 값에 따라 트랜잭션 로그 파일이 축소되며 데이터베이스가 축소된 후 25%(20MB)의 사용 가능한 공간이 남아 있게 됩니다.

  2. 쿼리 분석기를 사용하여 SQL Server에 연결한 후 다음 Transact-SQL 명령을 실행합니다.

       dbcc shrinkdatabase (tempdb, 'target percent') 
       -- This command shrinks the tempdb database
    

tempdb 데이터베이스에서 DBCC SHRINKDATABASE 명령을 사용할 때는 몇 가지 제한이 적용됩니다. 데이터 및 로그 파일의 대상 크기는 MODIFY FILE 옵션이나 명령을 사용하는 ALTER DATABASE 등의 파일 크기 변경 작업을 통해 명시적으로 설정한 마지막 크기나 데이터베이스를 만들 때 지정한 크기보다 작을 수 없습니다. 그리고 DBCC SHRINKDATABASE를 사용할 때는 target_percentage 매개 변수를 계산할 때 현재 사용된 공간을 알아야 한다는 제한도 있습니다.

방법 3: DBCC SHRINKFILE 명령 사용
DBCC SHRINKFILE 명령을 사용하여 개별 tempdb 파일을 축소할 수 있습니다. DBCC SHRINKFILE은 같은 데이터베이스에 속한 다른 파일에는 영향을 주지 않고 개별 데이터베이스 파일에 대해 사용할 수 있으므로 DBCC SHRINKDATABASE보다 유동적으로 활용할 수 있습니다. DBCC SHRINKFILE은 target size 매개 변수를 수신합니다. 이 매개 변수는 데이터베이스 파일의 원하는 최종 크기입니다.

  1. 기본 데이터 파일(tempdb.mdf), 로그 파일(templog.ldf) 및 tempdb에 더 추가한 파일의 원하는 크기를 결정합니다. 이러한 파일에서 사용되는 공간이 원하는 대상 크기 이하여야 합니다.

  2. 쿼리 분석기를 사용하여 SQL Server에 연결한 후 축소할 특정 데이터베이스 파일에 대해 다음 Transact-SQL 명령을 실행합니다.

       use tempdb
       go
    
       dbcc shrinkfile (tempdev, 'target size in MB')
       go
       -- this command shrinks the primary data file
    
       dbcc shrinkfile (templog, 'target size in MB')
       go
       -- this command shrinks the log file, examine the last paragraph.
    

DBCC SHRINKFILE 사용 시에는 파일 크기를 원래 크기보다 작게 줄일 수 있다는 장점이 있습니다. 모든 데이터 파일이나 로그 파일에 대해 DBCC SHRINKFILE을 실행할 수 있습니다. 하지만 DBCC SHRINKFILE 사용 시에는 데이터베이스를 model 데이터베이스의 크기보다 작게 줄일 수는 없다는 제한이 있습니다.

축소 작업 실행 시 발생하는 2501 및 8909 오류

사용 중인 tempdb 데이터베이스를 DBCC SHRINKDATABASE 또는 DBCC SHRINKFILE 명령으로 축소하려고 하면 사용하는 SQL Server 버전에 따라 다음과 같은 메시지가 표시될 수 있습니다.

SQL Server 2005 이상 버전

서버: 메시지 8909, 수준 16, 상태 1, 줄 1 테이블 오류: 개체 ID 0, 인덱스 ID -1, 파티션 ID 0, 할당 단위 ID 0(유형 알 수 없음), 페이지 ID(6:8040)의 페이지 헤더에 잘못된 페이지 ID가 있습니다. 페이지 헤더의 PageId = (0:0).


SQL Server 2000

서버: 메시지 2501, 수준 16, 상태 1, 줄 1 '1525580473' 테이블을 찾을 수 없습니다. sysobjects를 확인하십시오.

서버: 메시지 8909, 수준 16, 상태 1, 줄 0 테이블 손상: 개체 ID 1, 인덱스 ID 0, 페이지 ID %S_PGID. 페이지 헤더의 PageId = %S_PGID.

참고 사항

  • 이러한 오류가 발생한다고 해서 tempdb가 실제로 손상된 것은 아닙니다. 그러나 오류 8909와 같은 실제 데이터 손상 오류가 발생하는 다른 원인이 있을 수 있으며, 이러한 원인에는 I/O 하위 시스템 문제가 포함됩니다. 그러므로 축소 작업 외부에서 오류가 발생하는 경우 추가로 조사를 해야 합니다.

  • SQL Server 2005 이상 버전에서는 축소 작업을 실행하는 사용자나 응용 프로그램으로 8909 메시지가 반환되지만 축소 작업은 실패하지 않습니다.

  • SQL Server 2000 이하 버전에서는 이러한 오류가 발생하면 축소 작업이 실패합니다. 그러므로 tempdb 데이터베이스를 축소하려면 SQL Server를 다시 시작하여 tempdb를 다시 만들어야 합니다.

참조

데이터베이스 축소

추가 도움이 필요하신가요?

기술 향상
교육 살펴보기
새로운 기능 우선 가져오기
Microsoft Insider 참가

이 정보가 유용한가요?

소중한 의견에 감사드립니다.

피드백을 주셔서 감사합니다. Office 지원 에이전트와 연락하는 것이 도움이 될 것 같습니다.

×