MSSQL에서 DB, LOG 축소 쿼리 / 공간 줄이기

출처: http://sqlmvp.tistory.com/1179  (SQL Server DBA 체크 리스트)

DB의 공간 줄이기

1) DB 공간 줄이기

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를 관리하는데 있어서 하나의 기능만을 배워서 적용할 경우 그 파장이 매우 큰 경우가 많다.

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

[출처] 10_SQL 2008 강좌 - (1)DB관리 - DB 축소(SHRINK)|작성자 SungWook Kang

---

(개인적 의견)

개인적으로 아래의 방법으로 했을때,

.mdf 파일은 줄어들지 않고, .log 파일만 줄어 들었네요.

USE Snowsharp -- DB명 GO EXEC SP_HELPFILE -- DB, LOG 파일 사이즈 확인 (축소 전 크기 확인) BACKUP LOG Snowsharp WITH TRUNCATE_ONLY -- DB 로그 축소 (6번 라인) //실행오류 BACKUP LOG Snowsharp WITH NO_LOG -- DB 로그 삭제 -- DB 축소, 최소 사이즈 10메가 (Snowsharp_Data: 파일명) DBCC SHRINKFILE (Snowsharp_Data, 10) GO -- LOG 축소, 최소 사이즈 10메가 (Snowsharp_LOG: 파일명) DBCC SHRINKFILE (Snowsharp_Log, 10) GO EXEC SP_HELPFILE -- DB, LOG 파일 사이즈 확인 (축소 후 크기 확인)

Msg 155, Level 15, State 1, Line 6

'TRUNCATE_ONLY' is not a recognized BACKUP option.

Cannot shrink log file 2 (Snowsharp_Log) because of minimum log space required.

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

---

MS SQL에서 데이터베이스(DB)와 로그(LOG) 크기를 축소 쿼리입니다. 데이터베이스를 운영하다 보면 로그가 무한으로 계속 늘어나는 경우가 많이 발생을 합니다.

 

 주기적으로 관리할 수 있도록 유지 관리 계획을 지정해 사용해야 합니다. 유지 관리 계획에서는 데이터베이스 백업, 데이터베이스 축소, 인덱스 다시 구성, 인덱스 다시 작성, 통계 업데이트 등의 작업을 주기적으로 실행할 수 있도록 할 수 있습니다. 유지 관리 계획에서 쿼리 문을 실행 하도록 할 수도 있습니다.

 

 본 티스토링에서는 쿼리문을 알려드리는 내용이라서 다른 내용은 넘어가도록 하겠습니다.

 

 데이터베이스(DB)와 로그(LOG) 크기를 축소하기 위해서는 아래 쿼리문을 실행 하시면 됩니다. ERPPM은 데이터베이스명으로 축소할 데이터베이스로 변경하시면 됩니다. ERPPM_LOG는 로그명입니다. "데이터베이스명_LOG"으로 대부분 되어 있습니다.

 

 로그명 확인이 어려우시면 EXEC SP_HELPFILE를 실행하셔서 확인을 하시면 됩니다. 아래는 테스트로 작업한 내역입니다. 축소 전 보다 크기가 줄어든 것을 볼 수 있습니다.

--- 쿼리문 --   USE ERPPM --DB명 GO   EXEC SP_HELPFILE --DB, LOG 파일 사이즈 확인(축소 전 크기 확인)   BACKUP LOG ERPPM WITH TRUNCATE_ONLY --DB 로그 축소 BACKUP LOG ERPPM WITH NO_LOG -- DB 로그 삭제   --DB 축소, 최소 사이즈 10메가(ERPPM : 파일명) DBCC SHRINKFILE (ERPPM, 10)  GO   --LOG 축소, 최소 사이즈 10메가(ERPPM_LOG : 파일명) DBCC SHRINKFILE (ERPPM_LOG, 10)  GO   EXEC SP_HELPFILE --DB, LOG 파일 사이즈 확인(축소 후 크기 확인)

출처: MS SQL에서 DB, LOG 축소 쿼리 http://www.dsun.kr/34 [Dsuny]