[MSSQL]데이터 파일 축소

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

 데이터파일 축소 실습

실제 사용하는 databae의 크기보다, db파일(mdf, ldf) 사이즈가 클 경우, dbcc shrinkfile

쿼리문을 통해, 사이즈를 줄이는 방법에 대해 포스팅 하도록 하겠습니다.

참고로, 공간 부족으로 인한 장애발생을 대비하기 위해, databae파일과 로그파일 위치 및 size는

check 하셔야 합니다.

   

1. jkmoon 데이터베이스를 생성, test테이블을 만들어 데이터베이스의 사이즈를

1GB로 늘렸습니다. 그런 다음 사이즈가 큰 test 테이블을 삭제하였습니다.

   

2. jkmoon 데이터베이스 파일과 로그파일을 확인 하였습니다.

   

3. 로그사이즈를 먼저 확인하도록 합니다. 1.3GB중에서 약 1.7% 사용 중입니다.

dbcc sqlperf (logspace);

   

4. jkmoon 데이터베이스 파일, 로그의 이름과 위치를 확인 합니다.

Sp_helpdb jkmoon;

   

5. 쿼리문을 수행하여, 10MB로 로그파일을 줄입니다.

Use jkmoon

Go

   

dbcc shrinkfile (jkmoon_log, 10);

   

Jkmoon_log.ldf 로그사이즈가 줄어든걸 확인 하였습니다.

   

6. 마찬가지로, database파일도 사이즈를 10MB로 줄입니다.

Use jkmoon

go

   

Dbcc shrinkfile (jkmoon, 10);