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

Small Business를 사용하는 고객은 Small Business 지원
(http://smallbusiness.support.microsoft.com)
사이트에서 추가적인 문제 해결 방법 및 학습 리소스를 찾을 수 있습니다.

이 문서에서는 tempdb 데이터베이스를 축소하여 마지막으로 구성한 크기보다 작게 만드는 데 사용할 수 있는 세 가지 방법에 대해 설명합니다. 첫 번째 방법은 tempdb 파일의 크기를 완벽하게 제어할 수 있지만 SQL Server를 다시 시작해야 합니다. 두 번째 방법은 몇 가지 제한은 있지만 tempdb를 전체적으로 축소하며 SQL Server를 다시 시작해야 할 수 있습니다. 세 번째 방법을 사용하면 tempdb의 개별 파일을 축소할 수 있습니다. 마지막 두 방법을 사용하려면 축소 작업 중 tempdb 데이터베이스를 사용하지 말아야 합니다.

참고 SQL Server 2005를 사용하는 경우에도 이러한 방법을 적용할 수 있습니다. 그러나 엔터프라이즈 관리자나 쿼리 분석기 대신 SQL Server Management Studio를 사용하여 이러한 작업을 수행해야 합니다.

Tempdb 정보

tempdb는 임시 작업 영역입니다. 여러 용도 중에서도 SQL Server는 다음과 같은 용도로 tempdb를 사용합니다.

명시적으로 만든 임시 테이블의 저장소
쿼리 처리와 저장 중 만들어진 중간 결과를 저장하는 작업 테이블
구체화된 정적 커서

SQL Server는 tempdb 트랜잭션 로그에 데이터베이스를 복구하는 동안 트랜잭션을 롤백하는 데는 충분하지만 트랜잭션을 다시 실행하지는 않는 정보만 기록합니다. 이 기능은 tempdb에서 INSERT 문의 성능을 향상시킵니다. 또한 SQL Server를 다시 시작할 때마다 tempdb를 다시 만들기 때문에 트랜잭션을 다시 수행하는 정보는 기록할 필요가 없습니다. 따라서 롤포워드 또는 롤백을 위한 트랜잭션이 없습니다. SQL Server를 시작하면 모델 데이터베이스의 사본을 사용하여 tempdb가 다시 만들어지고 마지막으로 구성한 크기로 다시 설정됩니다.

기본적으로 tempdb 데이터베이스는 필요에 따라 자동으로 증가하도록 구성됩니다. 이 데이터베이스는 언젠가는 원하는 크기보다 더 커질 수 있습니다. SQL Server를 다시 시작하면 마지막으로 구성한 크기로 tempdb 크기가 다시 설정됩니다. 구성된 크기는 MODIFY FILE 옵션을 사용한 ALTER DATABASE 또는 DBCC SHRINKFILE 문 같은 파일 크기 변경 작업을 통해 명시적으로 설정한 마지막 크기입니다. 이 문서에서는 tempdb를 축소하여 마지막으로 구성한 크기보다 작게 만드는 데 사용할 수 있는 세 가지 방법에 대해 설명합니다.

Tempdb를 축소하는 방법 1

이 방법을 사용하려면 SQL Server를 다시 시작해야 합니다.

SQL Server를 중지합니다. 명령 프롬프트를 열고 다음 명령을 입력하여 SQL Server를 시작합니다.

sqlservr -c -f

-c 및 -f 매개 변수를 사용하면 SQL Server가 데이터 파일을 위해 1MB, 로그 파일을 위해 0.5MB의 tempdb 크기를 갖는 최소 구성 모드로 시작됩니다.

참고: SQL Server 명령 인스턴스를 사용하는 경우 적절한 폴더(Program Files\Microsoft SQL Server\MSSQL$instance name\Binn)로 변경하고 -s 스위치(-s%instance_name%)를 사용해야 합니다.

쿼리 분석기가 있는 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

명령 프롬프트 창에서 Ctrl-C를 눌러 SQL Server를 중지하고 SQL Server를 서비스로 다시 시작한 다음 Tempdb.mdf 및 Templog.ldf 파일 크기를 확인합니다.

이 방법의 한계는 기본 tempdb 논리 파일인 tempdev 및 templog에서만 작동한다는 것입니다. tempdb에 파일을 추가한 경우 SQL Server를 서비스로 다시 시작한 후에 축소할 수 있습니다. 시작하는 동안 모든 tempdb 파일이 다시 만들어집니다. 이러한 파일에는 아무런 내용이 없으므로 추가된 파일을 제거할 수 있습니다. tempdb에서 추가 파일을 제거하려면 ALTER DATABASE 명령을 REMOVE FILE 옵션과 함께 사용하십시오.

Tempdb를 축소하는 방법 2

DBCC SHRINKDATABASE 명령을 사용하여 tempdb 데이터베이스 전체를 축소합니다. DBCC SHRINKDATABASE는 데이터베이스를 축소한 후 데이터베이스 파일에 남는 여유 공간의 원하는 백분율인 매개 변수 target_percent를 받습니다. DBCC SHRINKDATABASE를 사용하는 경우 SQL Server를 다시 시작해야 할 수 있습니다.

중요: DBCC SHRINKDATABASE를 실행하면 tempdb 데이터베이스를 사용하여 다른 작업을 수행할 수 없습니다. DBCC SHRINKDATABASE를 실행하는 동안 다른 프로세스에서 tempdb를 사용할 수 없도록 하려면 SQL Server를 단일 사용자 모드에서 시작해야 합니다. 자세한 내용은 본 문서의 Tempdb를 사용하는 동안 DBCC SHRINKDATABASE 또는 DBCCSHRINKFILE의 실행 효과 절을 참조하십시오.

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이고 이제 80MB를 20MB로 나누면 25%이며 이것이 target_percent입니다. 트랜잭션 로그 파일은 이에 따라 축소되어, 데이터베이스를 축소한 후에는 25% 또는 20MB의 여유 공간이 남습니다.

쿼리 분석기가 있는 SQL Server에 연결하고 다음 Transact-SQL 명령을 실행합니다.

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

tempdb 데이터베이스에서 DBCC SHRINKDATABASE 명령을 사용하는 데는 제한이 있습니다. 데이터와 로그 파일의 대상 크기는 데이터베이스를 만들 때 지정한 크기 또는 MODIFY FILE 옵션을 사용한 ALTER DATABASE 또는 DBCC SHRINKFILE 명령 같은 파일 크기 변경 작업으로 명시적으로 설정한 마지막 크기보다 작게 만들 수 없습니다. DBCC SHRINKDATABASE의 또 다른 제한은 target_percentage 매개 변수의 계산과 현재 사용된 공간에 대한 종속성입니다.

Tempdb를 축소하는 방법 3

명령 DBCC SHRINKFILE을 사용하여 개별 tempdb 파일을 축소합니다. DBCC SHRINKFILE은 같은 데이터베이스에 속하는 다른 파일에 영향을 주지 않고 단일 데이터베이스에서 사용할 수 있기 때문에 DBCC SHRINKDATABASE보다 유연성이 뛰어납니다. DBCC SHRINKFILE은 데이터베이스 파일의 원하는 최종 크기인 대상 크기 매개 변수를 받습니다.

중요: DBCC SHRINKFILE 명령을 실행하는 동안에는 tempdb 데이터베이스에서 아무런 작업도 하지 말아야 합니다. DBCC SHRINKFILE을 실행하는 동안 다른 프로세스가 tempdb를 사용할 수 없도록 하려면 SQL Server를 단일 사용자 모드에서 다시 시작해야 합니다. DBCC SHRINKFILE에 대한 자세한 내용은 본 문서의 Tempdb를 사용하는 동안 DBCC SHRINKDATABASE 또는 DBCCSHRINKFILE의 실행 효과 절을 참조하십시오.

주 데이터 파일(tempdb.mdf), 로그 파일(templog.ldf) 및/또는 tempdb에 추가한 파일에 대해 원하는 크기를 결정합니다. 파일에 사용되는 공간은 원하는 대상 크기보다 작거나 같아야 합니다.

쿼리 분석기가 있는 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, look at the last paragraph.

DBCC SHRINKFILE의 장점은 파일 크기를 원래 크기보다 작게 줄일 수 있다는 것입니다. 데이터 또는 로그 파일에서 DBCC SHRINKFILE을 실행할 수 있습니다 . DBCC SHRINKFILE의 한계는 모델 데이터베이스의 크기보다 데이터베이스를 더 작게 만들 수 없다는 것입니다.

SQL Server 7.0에서 트랜잭션 로그 축소는 지연되며 데이터베이스에서 축소 작업을 용이하게 하려면 로그 잘라내기 및 백업을 실행해야 합니다. 그러나 기본적으로 tempdb에는 trunc log on chkpt 옵션이 ON으로 설정되어 있습니다. 따라서 해당 데이터베이스에 대해서는 로그 잘라내기를 실행할 필요가 없습니다. SQL Server 7.0에서 데이터베이스 트랜잭션 로그를 축소하는 방법에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

256650

(http://support.microsoft.com/kb/256650/ )

INF: SQL Server 7.0 트랜잭션 로그를 줄이는 방법

Tempdb를 사용하는 동안 DBCC SHRINKDATABASE 또는 DBCCSHRINKFILE의 실행 효과

tempdb를 사용 중이고 DBCC SHRINKDATABASE 또는 DBCC SHRINKFILE 명령을 사용하여 축소를 시도하는 경우 다음 유형과 비슷한 여러 가지 일관성 오류가 나타날 수 있으며 축소 작업이 실패할 수 있습니다.

Server: Msg 2501, Level 16, State 1, Line 1 Could not find table named '1525580473'. Check sysobjects.

또는

Server: Msg 8909, Level 16, State 1, Line 0 Table Corrupt: Object ID 1, index ID 0, page ID %S_PGID. The PageId in the page header = %S_PGID.

오류 2501은 tempdb의 손상을 나타내는 것은 아니지만 축소 작업을 실패하게 만듭니다. 반면에 오류 8909는 tempdb 데이터베이스의 손상을 나타낼 수 있습니다. SQL Server를 다시 시작하여 tempdb를 다시 만들고 일관성 오류를 해결합니다. 그러나 오류 8909 같은 실제 데이터 손상 오류에는 다른 원인이 있을 수 있으며 여기에는 입/출력 하위 시스템 문제가 포함됩니다.

참조

SQL 서버 온라인 설명서 항목: "DBCC SHRINKFILE"; "DBCC SHRINKDATABASE"

Microsoft 제품 관련 기술 전문가들과 온라인으로 정보를 교환하시려면 Microsoft 뉴스 그룹
(http://support.microsoft.com/newsgroups/default.aspx)
에 참여하시기 바랍니다.