MsSQL Server_Shrink(쉬링크) / ①SHRINKDATABASE

DB를 관리하다 보면 로그 파일이 불필요하게 늘어나거나 디스크의 용량이 부족하여 공간을 확보해야 할 필요가 있다.

이럴때 사용 가능한 것이 Shrink이다. 

 

필자가 생각하기에 Shrink는 방청소의 개념이라고 생각하면 될 것 같다.

 

 

사전적의미로 줄어들다, 줄어들게 하다라고 씌여있다. 확실히 맞는 말이다. 불필요하게 남은 공간을 축소해서 확 줄여주니깐..

이번 포스트에서는 SHRINKDATABASE 기능을 알아 보도록하자. 차후 2번째 포스트에서는 SHRINKFILE을 알아 보도록 하겠다.

테스트를 위해서 데이터베이스를 한개 생성해 보도록하자.

USE master GO   CREATE DATABASE SHRINKDB ON PRIMARY(NAME = SHRINKDB,FILENAME = N'C:\SHRINK\SHRINKDB.MDF',SIZE = 5MB) LOG ON(NAME = SHRINKDB_LOG,FILENAME = N'C:\SHRINK\SHRINKDB_LOG.LDF',SIZE = 1MB)   Colored by Color Scripter

cs

 

주파일인 MDF파일은 5MB이고  LOG파일은 1MB이다.

 

잘 생성되었다. 본인이 원한 위치 그대로 잘 생성되었다.

테이블을 만든 후에 15MB정도 채워 넣어 보도록 하자.

USE SHRINKDB GO     CREATE TABLE TEST ( TEXT VARCHAR(1024) ) GO   DECLARE @I INT = 0 WHILE @I < 1024 * 15   BEGIN    INSERT INTO TEST VALUES (REPLICATE('A',1024))  SET @I += 1   END   Colored by Color Scripter

cs

 

아까 처음에 SHRINKDB를 만들었을때 5MB 이외에 + 하여 약 15MB정도가 늘어나서 21MB가 되었다.

 

 

지금 현재 SHRINKDB는 다음과 같은 상태로 되어 있다.

이번에는 'B'와 'C'를 각각 15MB씩 추가하는 쿼리를 작성해서 날려도록 하자.

DECLARE @I INT = 0 WHILE @I < 1024 * 15   BEGIN    INSERT INTO TEST VALUES (REPLICATE('B',1024))  SET @I += 1   END   SET @I = 0 WHILE @I < 1024 * 15   BEGIN    INSERT INTO TEST VALUES (REPLICATE('C',1024))  SET @I += 1   END Colored by Color Scripter

cs

 

아마 지금 내부적으로 다음과 같이 되어 있을 것이다.

 

용량의 약간의 오차는 있지만 로그 파일과 합쳐서 55MB의 용량으로 변경되었다.

 

이렇게 데이터베이스를 사용하다가 갑자기 SHRINKDB의 디스크 공간이 부족하여 공간이 필요하다고 가정해보도록 하자.

다음과 같이 데이터베이스 축소를 해보도록 하자.

 

 

이 기능은 데이터 파일과 로그 파일을 모두 축소 한다는 의미이다.

축소를 하고 전과 용량을 비교해보자.

 

 

별 차이가 없다. 이렇게 해서는 전혀 축소라고 할 수가 없다.

그 이유는 이렇다. 지금 보면 위쪽에 캡쳐 화면을 보면 사용가능한 공간이 0.45MB이다. 0%라고 나온다.

그러니깐 축소를 시킬 공간 자체가 없는 것이다. 이럴 때는 데이터베이스의 크기를 늘리는 방법을 써야한다.

 

이것은 어쩔수없이 디스크를 늘리는게 맞는 것이다. 왜? 모든 정보가 꽉꽉 잘 차있기 때문이다.

SHRINK가 필요한 것은 DB를 계속적으로 사용하다가 도중에 사용하는 것이다.

 

그 이유는 다음 테스트를 통해서 확인해 보도록 하자.

 

예를 들어서 사용자가 SHRINKDB를 어느정도 사용하였다고 생각해보자. 그리고 그 와중에 몇개의 데이터를 지웠을 것이다.

그렇다고 가정하고 B를 지워보도록 하자.

DELETE TEST WHERE SUBSTRING(TEXT,1,1)='B'

cs

 

B를 지우면 아마 내부적으로는 이렇게 되어 있을 것이다.

파일 사이즈를 확인해보자.

 

MDF는 별다른 변화가 없다. 하지만 LDF가 확 늘어났다.

DELETE문을 시행 하면서 로그파일에 기록된 것이다.

 

그렇다면 DMF는 왜 줄어들지 않을 것일까? 

바로 여기서 SHRINK가 필요한 것이다.

 

저 위쪽에 빈공간을 줄이는 것이 SHRINK이다.

EXEC sp_spaceused

cs

아까 데이터베이스의 공간이 얼마나 남았는지 보는 UI를 위의 쿼리로 실행하면 다음과 같이 나온다.

MDF,LDF파일 2개를 합친 데이터베이스의 전체 크기 약 96MB로 나오고 있다.

이제 SHRINK를 다시 실행해 보도록 하자.

이것을 실행하면 이제 우리는 B가 지워졌던 공간을 돌려받을 수 있다.

물론 LDF파일에 기록된 DELETE기록도 돌려 받을 수 있다.

 

여기서 SHRINK는 2가지 방법으로 나누어 지는데 다음과 같이 설명 할 수 있다.

 

1. DBCC SHRINKDATABASE : 현재 데이터베이스에 소속된 모든 데이터 파일과 로그  파일에 적용된다.

2. DBCC SHRINKFILE : 특정 파일만을 축소 시키는 것

 

SHRINKDATABASE을 MSDN에서 한번 찾아보도록 하자.

 

 

구분을 보고 옵션을 확인해보도록 하자.

 

1. database_name줄은 데이터베이스의 명칭 또는 DBID를 입력해 줄이고자 하는 데이터베이스를 입력하는 것이다.

SP_HELPDB

cs

 

위의 SP_HELPDB 를 통해서 간단히 확인이 가능하다.

 

2. target_percent는 줄인 후에 남기고자 하는 공간을 뜻한다. 예를 들어서 이런 상황이 발생 할 수 있다. 

데이터베이스가 너무 빈번하게 불어나서 불필요하게 로그 파일이 증가하고 가끔 끊김 현상(데이터베이스 공간확장을 위해서)이 발생 하기도 할 경우 좀 더 여유공간을 늘리면서 축소를 할 수 있다.

 

3. NOTRUNCATE | TRUNCATEONLY는 중요한 옵션이다. 

 

 

위의 그림을 보면서 이해하면 빠를 것이다.

 

NOTRUNCATE는 아까 B가 지워지고 A와 C사이의 불필요하게 남은 빈 공간을 꾹꾹 눌러서(더 좋은 표현을 못 찾겠다.) 모두 몰아준다.

아까 우리가 축소를 하였을때 전혀 축소가 안되었었는데 바로 이것을 다시 재배치 하는 것이 바로 NOTRUNCATE  이다.

 

그리고 TRUNCATEONLY는 위의 그림에서 아무런 기능을 하지 못하는 것 같은데 당연하다. 위와 같이 되어 있을 경우 TRUNCATEONLY는 아무런 동작을 할 수 없다. 이것은 우리가 아까 빈공간이 축소되지 않았을때 그냥 축소를 싫행한것과 같은 상황이다. TRUNCATEONLY는 

NOTRUNCATE 가 정리한 후 뒤쪽에 남은 공간을 다시 싹 비워주는 역활을 한다. 

 

다음과 같이 동작 한다고 생각하면 될 것이다.

 

 

위의 그림과 같이 순차적으로 동작하는 것이다.

 

이것을 실 생활로 이야기 하면 이렇다. 집을 비워야 할때 정리 및 청소를 해주지 않으면 다음 집주인이 가만히 있겠는가? 당연히 계약하지 않을 것읻다. 그러므로 NOTRUNCATE가 방 청소를 해주고 TRUNCATEONLY가 싹 방을 비워 주는 것이다. 

 

NOTRUNCATE가 청소업체 , TRUNCATEONLY가 이사업체로 생각하면 이해가 쉬울 것이다. 

 

이제 진짜로 SHRINK를 실행해 보도록 하자.

DBCC SHRINKDATABASE(SHRINKDB)

cs

실행하면 다음과 같이 결과값이 나온다.

위의 설명은 MSDN에 잘 나와 있으므로 생략하도록 하겠다.

그림이 잘 안보이면 클릭해서 아래의 링크를 참조하자. (링크) 

 

MDF와 LDF를 확인해 보니 정상적으로 줄어 들었다.

 

내부적으로는 위의 그림처럼 정리가 되었을 것이다.

그리고 마지막으로 다음과 같이 옵션을 주면 현재 공간에 10%를 뒤쪽에 남기겠다는 뜻이 된다.

DBCC SHRINKDATABASE(SHRINKDB,10)

cs

아마 옵션을 주고 실행다면 위와 같이 될 것이다.

 

이상으로 이번 포스트를 마치며 다음 포스트에서는 SHRINKFILE에 대해서 알아 보도록 하겠다.