SQL Server 데이터 파일 축소

SQL Server 2005 에서 데이터(mdf,ndf) 파일을 축소하는 방법에 대해서 아래와 같이 정리하였습니다.

 

데이터 파일의 할당 정보는 DBCC SHOWFILESTATS 명령을 사용하여 확인할 수 있습니다. 트랜잭션 로그 파일의 현재 사용 가능한 빈공간을 조회할 때 DBCC SQLPERF(LOGSPACE) 명령을 사용하면 됩니다.

USE LAIGO

GO

DBCC SHOWFILESTATS 

아래와 같이 LAIGO 데이터베이스의 데이터 파일 LAIGO 의 전체 크기와 사용량을 확인할 수 있습니다.

Fileid      FileGroup   TotalExtents         UsedExtents       Name    FileName

--------------------------------------------------------------------------------------------------------------

1           1                880                      92                      LAIGO    D:\LAIGO.mdf

(1개 행 적용됨)

자, 산수시간입니다.

SQL Server 의 기본 저장 단위 Page 의 크기는 8KB, 이며 8개의 연속 페이지로 크기가 고정된 Extent 로 저장됩니다. 

1MB = 1024KB = 128(1024/8) Page = 16(1024/64) Extents

TotalExtents 값이 880 Extents 면, 1MB가 16 Extents 이므로 880/16 = 55 MB 가 됩니다.

UsedExtents 값이 92 면, 92/16 = 5.75 MB 가 됩니다.

즉, 현재 할당된 데이터 파일의 크기는 55MB 입니다만 실제 사용량은 5.75MB 라는 것을 알 수 있습니다. 


불필요한 공간을 줄이기 위해 데이터 파일을 축소하는 방법은 아래와 같습니다. LAIGO 파일의 크기를 10MB 로 축소하라는 의미입니다. 실제 TotalExtents 가 160 즉, 10MB 로 줄었습니다.

DBCC SHRINKFILE (LAIGO, 10)

Fileid      FileGroup   TotalExtents         UsedExtents       Name    FileName

--------------------------------------------------------------------------------------------------------------

1           1                 160                     92                      LAIGO    D:\LAIGO.mdf

(1개 행 적용됨)

TRUNCATEONLY 를 사용해 보면 어떨까요?

DBCC SHRINKFILE (LAIGO, TRUNCATEONLY)

DBCC SHOWFILESTATS 

아직 사용되지 않은 뒷부분 빈공간이 싹뚝 잘려 나갔습니다.
 

Fileid      FileGroup   TotalExtents         UsedExtents       Name    FileName

--------------------------------------------------------------------------------------------------------------

1           1                92                        92                     LAIGO    D:\LAIGO.mdf

(1개 행 적용됨)

Single user 모드가 아니어도 작업은 가능합니다만 가능하면 Single User 모드가 아닌 상태에서 Deadlock 이 발생하는 사례가 있으므로 급한 상황이 아니라면 Single user 모드에서 작업하는 게 좋을 것 같다는 의견입니다. 만약 작업이 실패할 경우에는 SQL Error Log 을 살펴봐야 할 것 같습니다.

ALTER DATABASE LAIGO SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

DBCC SHRINKFILE(LAIGO_DATA1, 3000) -- 3GB

ALTER DATABASE LAIGO SET MULTI_USER
GO

데이터 파일을 축소한 뒤에는 Index 의 단편화가 발생할 수 있으므로 인덱스 단편화 상태를 꼭 점검해 보시기 바랍니다.


[참고자료]

DBCC SHRINKFILE(Transact-SQL)
http://msdn.microsoft.com/ko-kr/library/ms189493(SQL.90).aspx

작성자 : Lai Go / 작성일자 : 2009.09.16

 

 

출처 - http://laigo.kr/351