[MSSQL] DBCC SHRINKFILE - DB 데이터/로그 파일 축소하기 - 1편

 

SQL Server의 데이터베이스는 파일 그룹으로 이루어져 있다.

파일 그룹은 데이터 파일들이 저장될 그룹을 의미하는데 이 그룹은 또 데이터 파일(MDF or NDF) + 로그 파일(LDF)을 의미한다.

 

해당 포스트는 총 2편으로 작성할 생각이다.

• DBCC SHRINKFILE - DB 데이터/로그 파일 축소하기 - 1편 ( 파일의 초기사이즈, 자동증가, 자동감소?? )
• DBCC SHRINKFILE - DB 데이터/로그 파일 축소하기 - 2편 ( 데이터파일을 축소하기. )

 

 

파일의 크기/자동 증가

 

파일그룹, 파일, 테이블생성

데이터베이스를 생성할 때 아래의 쿼리 또는 사진을 한 번쯤은 무조건 보았을 것이다.

그림1 - 새 데이터베이스 생성

CREATE DATABASE JJH
    ON PRIMARY 
    (
        NAME='JJH_PRIMARY'
        , FILENAME = '/var/opt/mssql/data/JJH/JJH.mdf'
        , SIZE = 10MB
        , MAXSIZE = UNLIMITED
        , FILEGROWTH = 1MB
    ),
    FILEGROUP JJH_FG1
    (
        NAME = 'JJH_FG1'
        , FILENAME = '/var/opt/mssql/data/JJH/JJH_FG1.ndf'
        , SIZE = 10MB
        , MAXSIZE = UNLIMITED
        , FILEGROWTH = 1MB
    )
    LOG ON 
    (
        NAME = 'JJH_LOG'
        , FILENAME = '/var/opt/mssql/data/JJH/JJH_LOG.ldf'
        , SIZE = 10MB
        , MAXSIZE = UNLIMITED
        , FILEGROWTH = 1MB
    )

ALTER DATABASE JJH
MODIFY FILEGROUP JJH_FG1 DEFAULT;

 

위 그림 1 or 쿼리를 보게 되면 파일의 초기 크기와 자동 증가량을 설정하게 된다.

 

그렇다. 데이터 파일은 초기 크기로 생성되고 일정 사용량이 초과되면 자동 증가량만큼 운영체제에게 할당받는다.

 

아래 쿼리는 파일 그룹을 생성 후 데이터베이스에 새로운 데이터 파일을 생성하여 매핑시켜준다.

-- 파일 그룹 FILE_TEST_GROUP생성.
ALTER DATABASE CMS_DEV
ADD FILEGROUP FILE_TEST_GROUP;

-- 데이터베이스 파일 생성 -> 파일그룹 매핑.
ALTER DATABASE CMS_DEV
ADD FILE
(
    NAME = FILE_SIZE_TEST,
    FILENAME = '/var/opt/mssql/data/FILE_SIZE_TEST.ndf',
    SIZE = 5MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1MB
) TO FILEGROUP FILE_TEST_GROUP

-- 테스트 테이블 생성
-- 테스트 테이블의 데이터는 FILE_TEST_GROUP파일그룹에 데이터를 보관.
CREATE TABLE FILE_SIZE_TEST
(
    id INT,
    name CHAR(8000)
)ON FILE_TEST_GROUP

 

파일의 초기 사이즈

 

생성만 했는데 데이터 파일의 용량은 실제로 5MB일까???

[root@e7ca3a728a4c data]# pwd
/var/opt/mssql/data

[root@e7ca3a728a4c data]# ls -alh
total 225M
drwxr-xr-x 4 mssql mssql 4.0K May  3 10:04 .
drwxrwx--- 6 mssql mssql 4.0K Feb 21 09:44 ..
drwxrwx--- 2 mssql mssql 4.0K Feb 21 10:25 AdventureWorks
-rw-rw-r-- 1 jjh   jjh    48M Feb 21 10:19 AdventureWorks2017.bak
-rw-rw---- 1 mssql mssql  72M May  3 10:04 CMS_DEV_log.ldf
-rw-rw---- 1 mssql mssql 8.0M May  3 10:04 CMS_DEV.mdf
-rw-rw---- 1 mssql mssql 8.0M May  3 09:57 CMS_log.ldf
-rw-rw---- 1 mssql mssql 8.0M May  3 09:57 CMS.mdf
-rw-rw---- 1 mssql mssql 5.0M May  3 10:04 FILE_SIZE_TEST.ndf
....
drwxrwx--- 2 mssql mssql 4.0K Feb 21 09:45 JJH

 

실제로 FILE_SIZE_TEST.ndf의 파일 용량이 5MB이다....  그럼 실제로 사용하고 있는 용량은 얼마나 될까?

그림2 - 데이터베이스 파일의 세부정보 조회

 

그림 2를 보면 FILE_TEST_GROUP 파일 그룹은  실제로 사용하고 있는 용량이 0MB이다...

 

데이터베이스는 실시간으로 디스크를 할당받으면 I/O 속도가 너무 느리기 때문에 미리 할당을 받아놓고 사용을 한다.

 

 

데이터/로그 파일의 자동 감소??

 

그렇다면 데이터/로그 파일은 자동 증가량은 설정하는데 자동 감소량 설정은 어디서 할까?

위에서 생성한 FILE_TEST_GROUP.ndf 파일을 증가시켜보았다.

그림3 - 증가된 파일그룹 사이즈

 

[root@e7ca3a728a4c data]# pwd    
/var/opt/mssql/data

[root@e7ca3a728a4c data]# ls -alh
total 440M
drwxr-xr-x 4 mssql mssql 4.0K May  3 10:04 .
drwxrwx--- 6 mssql mssql 4.0K Feb 21 09:44 ..
drwxrwx--- 2 mssql mssql 4.0K Feb 21 10:25 AdventureWorks
-rw-rw-r-- 1 jjh   jjh    48M Feb 21 10:19 AdventureWorks2017.bak
-rw-rw---- 1 mssql mssql 136M May  3 10:15 CMS_DEV_log.ldf
-rw-rw---- 1 mssql mssql 8.0M May  3 10:15 CMS_DEV.mdf
-rw-rw---- 1 mssql mssql 8.0M May  3 09:57 CMS_log.ldf
-rw-rw---- 1 mssql mssql 8.0M May  3 09:57 CMS.mdf
-rw-rw---- 1 mssql mssql  92M May  3 10:15 FILE_SIZE_TEST.ndf
....
drwxrwx--- 2 mssql mssql 4.0K Feb 21 09:45 JJH

 

92MB까지 증가시켰다.    이 시점에서 테이블의 데이터를 모두 삭제해보겠다.

 

DELETE FILE_SIZE_TEST1

----------------------------------------------------------------
(11648개 행이 영향을 받음)

 

과연? 파일의 사이즈도 줄어들었을까??

그림4 - 줄어들지 않는 파일 사이즈

[root@e7ca3a728a4c data]# pwd    
/var/opt/mssql/data

[root@e7ca3a728a4c data]# ls -alh
total 568M
drwxr-xr-x 4 mssql mssql 4.0K May  3 10:04 .
drwxrwx--- 6 mssql mssql 4.0K Feb 21 09:44 ..
drwxrwx--- 2 mssql mssql 4.0K Feb 21 10:25 AdventureWorks
-rw-rw-r-- 1 jjh   jjh    48M Feb 21 10:19 AdventureWorks2017.bak
-rw-rw---- 1 mssql mssql 264M May  3 10:17 CMS_DEV_log.ldf
-rw-rw---- 1 mssql mssql 8.0M May  3 10:17 CMS_DEV.mdf
-rw-rw---- 1 mssql mssql 8.0M May  3 09:57 CMS_log.ldf
-rw-rw---- 1 mssql mssql 8.0M May  3 09:57 CMS.mdf
-rw-rw---- 1 mssql mssql  92M May  3 10:17 FILE_SIZE_TEST.ndf
....
drwxrwx--- 2 mssql mssql 4.0K Feb 21 09:45 JJH

 

파일의 데이터를 모두 삭제해도 FILE_SIZE_TEST.ndf 파일의 크기는 줄어들지 않는다.........

 

[DBCC SHRINKFILE - DB 데이터/로그 파일 축소하기 - 2편] 포스트에서 데이터/로그 파일의 쓰지 않는 공간을 비우고 파일을 축소시키는 법을 알아보겠다.