데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

데이터베이스 관리 – DB축소

 

데이터베이스를 생성하여 운용하다보면 데이터 사이즈는 증가한다.

하지만 데이터 파일의 모든 크기를 사용하지 않을 수도 있다.

즉, 데이터를 삭제하면 데이터 파일 내에 빈 공간이 존재하게 된다. 만약 하드디스크 공간이 없거나 액세스 범위(물리적인 스캔 위치)를 줄이기 위하여 DB축소 작업을 진행하는데 어떻게 하는 것인지 또한 어떤 종류가 있는지 알아 본다.

 

DB 축소하기.

 

DB의 크기는 데이터베이스를 생성할 때 미리 지정할 수 있다고 배웠다.

(08_데이터베이스 생성 편 참고)

또한 데이터베이스의 자동증가 옵션을 통하여 사용에 따라 지속적으로 데이터베이스의 크기를 증가 할 수 있다고 하였다.

 

그렇다면 DB의 사이즈가 늘기만 할까? 만약 불필요한 데이터를 삭제하여 디스크 공간을 절약하고(이는 디스크 사이즈가 작을 때 사용. 현업에서는 잘 사용하지 않는다.) 또는 빈공간을 제거하여 디스크의 물리적인 파일 위치의 집적도를 높임으로써 스캔의 범위를 줄이고자 할 때에는 어떻게 할까?

 

그래서 DB축소에 대해서 알아 보기로 한다.

 

1. 데이터 베이스를 생성한다. (본 시리즈를 따라 실습하는 독자라면 TestDB가 생성되어 있을 것이다.)

 

TestDB를 생성한다. 필자는 TestDB라는 이름으로 SQL디폴트 값으로 생성하였다.

MDF : 사이즈 2M, 자동증가 1M

LDF : 사이즈 1M, 자동증가 10%

 

 

실제 파일 크기를 확인한다.

 

SSMS에서 확인한 결과와 약간의 오차는 있지만 비슷한 것을 확인 할 수 있다.

 

2. 테스트 테이블을 생성하여 데이터를 입력하여 DB사이즈를 증가 시킨다.

실제 데이터 파일이 증가 된 것을 확인 할 수 있다.

 

 

이런식으로 tbl_Test에 'B'데이터, 'C' 데이터를 입력하자.

 

데이터 사이즈가 늘어 난 것을 확인 할 수 있다.

 

 

현재 tbl_Test의 데이터는 아래의 상태로 들어 있을 것이다.

 

 

3. 데이터를 삭제 한다.

물리적인 파일을 확인 한다.

사이즈가 줄어들지 않았다. 데이터파일이 삭제 되었는데 왜 사이즈가 줄어 들지 않았을까?

실제 DB에서 사용하는 공간을 체크해 보았다.

24.6메가의 여유 공간이 확인 되었다.

 

그런데 왜 사이즈가 줄지 않는 것인가?

현재 tbl_Test의 데이터는 아래의 상태로 들어 있을 것이다.

즉 사용하지 않는 공간 또한 물리적인 데이터 크기로 자리 잡고 있다.

 

 

4. 데이터베이스 축소.

(Msdn 참고

Shrink Database : http://msdn.microsoft.com/ko-kr/library/ms190488(SQL.100).aspx

Shrink File : http://msdn.microsoft.com/ko-kr/library/ms189493(v=SQL.100).aspx

)

 

1)SSMS 사용하기.

해당 DB에서 마우스 오른 쪽 클릭 – [태스크] – [축소] – [데이터베이스 | 파일]

[데이터베이스] : 해당 데이터베이스의 데이터 파일과 로그 파일을 모두 축소 한다.

[파일] : 해당 데이터베이스에 대해 지정한 데이터 또는 로그 파일의 크기를 축소하거나 같은 파일 그룹의 다른 파일로 이동하여 파일을 비우고 데이터베이스에서 제거 한다.

 

 

 

DB사이즈의 축소 테스트 이므로 여유공간 0%를 선택한다. (여유공간을 남기지 않겠다는 뜻이다.)

현업에서는 어느정도 여유공간을 남겨 둠으로써 DB사이즈 증가시 IO의 병목을 줄일 수 있다.

 

데이터 파일의 사이즈가 축소 된 것을 확인 할 수 있다. 물론 실제 파일의 크기 또한 줄어 든 것을 확인 할 수 있다.

 

2) 쿼리 이용하기.

구문

DBCC SHRINKDATABASE

( database_name | database_id | 0

[ , target_percent ]

[ , { NOTRUNCATE | TRUNCATEONLY } ]

)

[ WITH NO_INFOMSGS ]

 

 

NOTRUNCATE 옵션 : 데이터를 정리만 할 뿐 파일의 크기를 줄이지 않는다.

 

TRUNCATEONLY 옵션 : 데이터는 정리하지 않고 뒤의 남는 부분만 줄인다.

 

 

옵션 설정없이 디폴트로 사용하게 되면 위의 NOTRUNCATE 와 TRUNCATEONLY옵션이 차례대로 적용되어 실행 된다.

 

 

이렇게 하여 DB의 축소하는 것을 살펴 보았다.

하지만 과연 현업에서 DB를 축소할 일이 얼마나 있을까?

또한 DB를 축소하고 나면 다른 문제는 발생하지 않을까?

 

필자가 해외 출장에 겪은 일화 이다.

 

아무런 작업을 하지 않는 DB에 퍼포먼스가 나오지 않는다.

실제 DB를 살펴봐도 아무런 작업이나 부하가 없다. 인덱스도 잘 타고 있다.

담당 DBA와 이야기 중에 DB축소 작업을 진행 하였다는 정보를 알아 내었다.

어떤 문제가 있었을까?

 

예를 들어 순차적으로 데이터가 들어 있고 인덱스가 있다고 가정하자.

이때 오래된 데이터를 삭제하면서 공간을 절약하고자 축소작업을 하였다.

데이터가 이동한다. 인덱스의 위치는 어떻게 될까? 데이터의 이동으로 인하여 데이터 순서가 엇갈려 있을 수 있다. 실행계획 상으로는 인덱스를 타고 있지만 물리적으로는 Random I/O 하고 있을지 모른다.

필자는 담당DBA에게 통계 업데이트 및 인덱스 재정렬 작업을 권장하였다.

 

그리고 왠만하면 DB축소 하지 말라고 권고 하였다. (디스크가 500GB넘게 여유 공간이 있는데 왜자꾸 하는지 모르겠다. 주로 아시아 권역이 DB축소를 좋아하는 듯 하다.)

 

DB를 관리하는데 있어서 하나의 기능만을 배워서 적용할 경우 그 파장이 매우 큰 경우가 많다.

항상 여러가지 상황을 염두에 두고 철저한 테스트 및 기술을 습득하여 사용하길 바란다.







강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp



No. Subject Author Date Views
2191 MSSQL과 Oracle 함수 비교 3탄 - 9가지 함수(마지막) [5] 쓸만한게없네(윤선식) 2010.11.08 81624
2190 DB 오류검사기록 테이블, 백업할때 오류정보 기록테이블 [3] 차주언 2011.07.20 56462
» 10_SQL 2008 강좌 - (1) DB관리 - DB 축소(SHRINK) [8] jevida(강성욱) 2011.03.01 50904
2188 Linked Server Open Query 이용 시 USE 명령어 사용하기 [1] 쓸만한게없네(윤선식) 2012.11.07 50673
2187 SSMS 줄 번호 표시 jevida(강성욱) 2015.03.26 46959
2186 sa 비밀번호를 잊어먹었을 때 대처방법 [8] 이스트럭(강동운) 2011.06.13 46880
2185 SMO를 이용한 DB백업 [3] 우주인(김재훈) 2011.03.17 45009
2184 [TIP]MSSQL 와일드 카드를 이용한 검색과 주의점 [16] 쓸만한게없네 2010.10.12 44281
2183 프로파일러 사용법. (완전 초보자 기준!) [10] jevida(강성욱) 2011.04.26 43405
2182 SSMS 단축키. [12] 쓸만한게없네(윤선식) 2011.02.07 43118
2181 SQL Server 로긴 유저 및 각 DB별 사용자 권한 조회 [1] 문연군 2010.12.20 42306
2180 MSSQL과 Oracle 함수 비교 2탄 - 9가지 함수 [10] 쓸만한게없네(윤선식) 2010.10.27 41901
2179 Microsoft.ACE.OLEDB.12.0 을 이용한 엑셀 2010 직접쿼리하기 [3] 열이 2013.03.20 41528
2178 DBCC TRACE ... [4] 이스트럭(강동운) 2012.04.13 40940
2177 SQL Server 와 MySQL 문자열 함수 비교 10 가지 [5] 이호엽 2010.12.29 38236
2176 SQL2012는 SSMS의 시작 옵션(Startup option) 설정 위치가 바뀌었어요. [1] 코난(김대우) 2012.01.13 37459
2175 MSSQL과 Oracle 함수 비교 - 첫번째 10가지 함수. [7] 쓸만한게없네(윤선식) 2010.10.25 35834
2174 CONVERT 스타일 별 결과 값 [3] Alucard(강산아) 2010.10.14 35508
2173 트랜잭션 열린넘 있으면 로그백업할때 사이즈가 커진다!! [5] 차주언 2009.12.02 33538
2172 초간단 - 알만한 사람은 다 아는 읽고, 쓰고, 실행하는 권한만 주기 스크립트. [2] 쓸만한게없네(윤선식) 2011.07.21 32939





XE Login