안녕하세요. SQLER의 코난 김대우입니다.

이번 강좌에서는, 4-3. 데이터베이스 크기조절을 진행 하겠습니다.


SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록
 

이번에 진행할 강좌는 데이터베이스 크기 조절입니다.

 

 

 

TL;DR

데이터베이스 크기를 늘리거나 줄이는 작업을 진행합니다. 늘릴 경우, 파일 그룹 추가와 데이터 파일 추가도 가능하며, 줄이는 과정은 DBCC 명령을 이용합니다.

 

제목처럼 데이터베이스 크기를 늘리거나 줄이는 작업과 파일그룹을 추가하거나 파일을 추가하는 작업도 크기 조절입니다.

 

 

데이터베이스 크기를 조절할 경우

데이터베이스 크기를 조절할 경우는 두 가지입니다. 
1. 크기를 늘리고 싶을 경우.
2. 크기를 줄이고 싶을 경우.


이 두 가지겠지요. 아마 대부분의 경우 데이터베이스의 크기를 늘릴 겁니다.


데이터베이스 크기를 늘리는 명령, 파일그룹이나 파일을 추가할 경우에는 ALTER DATABASE 구문을 사용합니다.

 

데이터베이스 크기 조절 구문

ALTER DATABASE { database_name | CURRENT }
{
    MODIFY NAME = new_database_name
  | COLLATE collation_name
  | <file_and_filegroup_options>
  | SET <option_spec> [ ,...n ] [ WITH <termination> ]
}
[;]


복잡해 보이지만 예제를 보면 쉽습니다. SSMS를 이용하는 방법은 간략히 소개해 드리고 가능한 쿼리로 하는 방법을 주로 말씀드리겠습니다.

 

 

데이터 파일 크기 늘리기

SSMS에서 데이터 파일 늘리기

53-1-데이터파일늘리기.png

이미지 - 데이터베이스 속성

 

데이터베이스에서의 “속성”을 클릭하고 데이터베이스 속성 윈도에서 파일탭으로 이동합니다. 이어서, 크기 및 자동증가 등을 수정할 수 있습니다. 데이터 파일과 로그 파일의 크기를 20M로 변경해 보세요. 변경하는 사이즈와 서버의 성능에 따라 약간 시간이 소요될 수 있습니다.

 

SQL 쿼리로 데이터 파일 늘리기

먼저, sp_helpdb 명령으로 데이터베이스 정보를 확인합니다.

EXEC sp_helpdb 'sqler_test';
GO


다음 SQL 쿼리로 데이터 파일과 로그 파일 크기를 늘립니다.

USE master;
GO

ALTER DATABASE sqler_test 
MODIFY FILE
    (NAME = sqler_test,
    SIZE = 40MB);
GO

ALTER DATABASE sqler_test 
MODIFY FILE
    (NAME = sqler_test_log,
    SIZE = 40MB);
GO

 

완료되면 sp_helpdb 명령으로 체크해 보세요. 데이터 파일과 로그 파일 크기가 40M로 늘어났습니다.

 


데이터 파일, 파일 그룹 추가

SSMS에서 데이터 파일 추가

53-2-데이터파일추가.png

이미지 - 데이터 파일 추가

 

SSMS의 데이터베이스 속성 - 파일 탭에서 추가 버튼을 누르고 데이터 파일을 추가할 수 있습니다. 여러 개의 데이터 파일 추가도 가능합니다. 완료되면 sp_helpdb 명령으로 체크해 보세요. 

 

SQL 쿼리로 데이터 파일 추가

아래 쿼리를 수행해 데이터 파일을 추가할 수 있습니다.

USE master;
GO

-- 데이터 파일 추가
ALTER DATABASE sqler_test
ADD FILE
(
    NAME = sqler_data3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\sqler_data3.ndf',
    SIZE = 20MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

-- 데이터 파일 추가 체크
EXEC sp_helpdb 'sqler_test';
GO

 

SQL 쿼리로 파일 그룹 추가, 데이터 파일 추가

파일 그룹 추가를 SSMS에서 하려면 파일 탭 바로 옆의 “파일 그룹” 탭에서 추가하고 데이터 파일을 추가하면 됩니다. SQL 쿼리로 수행하는 과정만 확인하겠습니다.

USE master;
GO

-- 파일 그룹 sqler_db_fg1 추가
ALTER DATABASE sqler_test
ADD FILEGROUP sqler_db_fg1;
GO

-- 2개의 데이터 파일(sqler_fg1_df1, sqler_fg1_df2)을 파일그룹 sqler_db_fg1에 추가
ALTER DATABASE sqler_test
ADD FILE
(
    NAME = sqler_fg1_df1,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\sqler_fg1_df1.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),  
(  
    NAME = sqler_fg1_df2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\sqler_fg1_df2.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)  
TO FILEGROUP sqler_db_fg1;
GO

-- 데이터베이스 확인
EXEC sp_helpdb 'sqler_test';
GO

 

sp_helpldb로 확인하면 파일 그룹과 데이터 파일이 파일그룹에 추가된 것을 볼 수 있습니다.

 

 

데이터베이스 크기 축소, 이름 변경

SQL 쿼리로 데이터베이스 크기 축소

데이터베이스 크기를 늘리는 작업을 진행했으니 이제 축소하는 작업을 진행합니다. (만약, 중요한 운영 시스템이라면, 먼저 백업을 수행하고 진행하세요.)

USE master;
GO

-- shrink_test 예제 데이터베이스 생성
CREATE DATABASE shrink_test;
GO

USE shrink_test;
GO

-- 현재 크기 확인
EXEC sp_spaceused; 
GO
 
database_name                     database_size      unallocated space
------------------------------ -------------- ------------------
shrink_test                       16.00 MB           4.10 MB

reserved           data               index_size         unused
------------------ ----------------- -------        ------------
2864 KB            1056 KB            1360 KB            448 KB

생성한 직후에는 이런 결과를 볼 수 있습니다.

 

약 16M 정도 크기입니다. 데이터베이스를 줄이려면 먼저 늘려야겠죠. 그럼 이 데이터베이스에 AdventureWorks.Sales.SalesOrderDetail 테이블을 SELECT INTO로 복사해 오겠습니다. 

 

USE shrink_test;
GO

-- SELECT INTO 수행 약 121,317건
SELECT * INTO dummy FROM AdventureWorks.Sales.SalesOrderDetail;
GO

SELECT top 10 * FROM dummy;
SELECT count(*) FROM dummy;
GO

-- 데이터베이스 크기 확인
EXEC sp_spaceused; 
GO

 

결과는 이렇게, dummy 테이블을 복사한 후, 16M의 초기 크기에서 144M로 자동증가 했습니다.

database_name               database_size      unallocated space
--------------------------- ----------------- ------------------
shrink_test                 144.00 MB          56.34 MB


reserved           data               index_size         unused
------------------ ------------------ ----------------- --------
15472 KB           13064 KB           1440 KB            968 KB

 

dummy 테이블을 삭제(DROP) 하고 다시 용량을 체크합니다. 한번 커진 데이터베이스는 자동으로 축소되지 않습니다.

 

-- 테이블을 drop 하고 크기를 확인하면?
DROP TABLE dummy;
GO

-- 데이터베이스 크기 확인
EXEC sp_spaceused; 
GO

database_name                 database_size      unallocated space
----------------------------- ----------------- ----------------
shrink_test                   144.00 MB          68.22 MB

 

데이터베이스 축소 명령을 수행합니다.

 

-- 데이터베이스 파일 줄이기 - 40% 여유 공간을 확보하는(줄이라는) 명령
DBCC SHRINKDATABASE (shrink_test, 40); 
GO

--약간 시간 소요
EXEC sp_spaceused; 
GO

database_name  database_size      unallocated space
-------------- ------------------ ------------------
shrink_test    80.00 MB           4.09 MB

 

약 40% 정도 축소되어 144M가 80M 정도로 줄어들었습니다. 

 

데이터베이스 로그 파일 줄이기

자주 받는 질문인 데이터베이스 로그 파일 크기 줄이기입니다. 데이터베이스 로그는 데이터의 변경이 기록되는 중요한 파일입니다. 이 데이터베이스 로그는 백업하면 “비워”집니다. 삭제되는 게 아니라 백업되어 비워지고, 백업 파일이나 장치로 기록됩니다.


운영하면서 한 번도 이 로그를 백업하지 않았다면, 로그 파일이 수기가까지 커져 속도가 느려지고 문제가 발생하는 경우가 종종 발생합니다. 


어찌어찌 검색해서 로그는 백업해 비웠는데 로그 파일이 줄어들지 않아 난감한 경우가 생기죠. 이럴 경우 아래 방법을 참조하세요. (만약, 운영 시스템이라면 반드시 먼저 백업을 수행하고 진행하세요.)

 

--로그 데이터를 쌓기 위한 dummy 테이블을 생성
CREATE TABLE dummy_table2
(
 dummy CHAR(1024)
);
GO

INSERT INTO dummy_table2 VALUES('');
GO

-- WHILE 루프를 돌면서 계속 수정해 로그파일 크기를 증가시킴.
SET NOCOUNT ON
DECLARE @i INT 
SET @i=0
WHILE ( @i < 1000)  -- 숫자를 조절
BEGIN
    UPDATE dummy_table2 SET dummy = NEWID()
    SET @i = @i + 1
END;
GO

EXEC sp_helpdb 'shrink_test';
GO

 

이렇게 반복 숫자를 변경하면서 크기를 체크하면, 로그 파일의 크기가 커진 것을 볼 수 있습니다. 

 

53-3-커진로그파일.png

이미지 - 커진 로그파일


데이터 파일은 8M인데 로그파일은 73M입니다. 수기가까지 커진 로그파일도 종종 봤습니다.

 

아래 SQL 구문을 수행해 로그 파일을 줄입니다.

-- 데이터베이스 로그 비우기(TRUNCATE_ONLY)는 SQL2008부터 삭제됨
-- BACKUP LOG shrink_test WITH TRUNCATE_ONLY
-- 복구 모델을 Simple로 변경 - 로그를 기록하지 않도록 DB를 설정
ALTER DATABASE shrink_test SET RECOVERY SIMPLE;
GO

 -- DBCC SHRINKFILE 로그 파일 1M로 줄이기 - 파일이 클 경우 시간이 오래 걸림
DBCC SHRINKFILE (shrink_test_log, 1); 
GO

EXEC sp_helpdb 'shrink_test';
GO

-- 다시 복구 모델을 FULL로 변경
ALTER DATABASE shrink_test SET RECOVERY FULL;
GO

 

53-4-작아진로그파일.png

이미지 - 작아진 로그파일


이렇게 로그 파일의 크기가 줄어든 것을 확인 가능합니다.

 

데이터베이스 이름 변경 - rename database

끝으로 데이터베이스의 이름을 변경하려 할 경우입니다. 거의 사용할 일이 없습니다. 만약 변경할 경우, 해당 데이터베이스를 사용하는 모든 애플리케이션에서 데이터베이스 이름 참조(일반적으로 카탈로그명)도 변경해야 하니 미리 검토합니다.
데이터베이스에 사용자가 있을 경우 rename이 제한됩니다. 필요하다면 데이터베이스 사용자 프로세스를 kill 하고 데이터베이스 이름변경을 진행합니다.

 

-- 데이터베이스 사용자를 자동 kill 하는 SQL 쿼리.
-- 중요 작업 프로세스가 있는지 미리 체크
USE master;
GO

DECLARE @kill varchar(max) = '';
SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(10), spid) + '; '
FROM master..sysprocesses 
WHERE spid > 50 AND dbid = DB_ID('shrink_test');  -- 데이터베이스 이름으로 변경
EXEC(@kill);
GO

-- 데이터베이스를 단일사용자 모드로 수정
ALTER DATABASE shrink_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
--데이터베이스 이름변경
ALTER DATABASE shrink_test MODIFY NAME = shrink_done;
GO 
--다시 다중사용자 모드로 수정
ALTER DATABASE shrink_done SET MULTI_USER;
GO

EXEC sp_helpdb;
GO


확인해 보면 shrink_test 데이터베이스가 shrink_done으로 이름이 변경된 것을 확인 가능합니다.
 

 

SQL 강좌 책 구매

강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다. 

 

책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022 

책구매링크.png

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 34935
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 17245
2254 SQL강좌: 6-2. 데이터 무결성 - 테이블 컬럼과 NULL 제약 코난(김대우) 2023.08.18 59
2253 SQL강좌: 6-1. 데이터 무결성 - 데이터 무결성 이해 file 코난(김대우) 2023.08.18 89
2252 SQL강좌: 5-4. 테이블 수정 file 코난(김대우) 2023.08.18 95
2251 SQL강좌: 5-3. 테이블 생성 file 코난(김대우) 2023.08.18 75
2250 SQL강좌: 5-2. 테이블 생성과 데이터형 선택 [1] 코난(김대우) 2023.08.18 54
2249 SQL강좌: 5-1. SQL Server 테이블 file 코난(김대우) 2023.08.18 75
2248 SQL강좌: 4-8. 시스템 카탈로그 엿보기 file 코난(김대우) 2023.08.18 72
2247 SQL강좌: 4-7. 최적의 데이터베이스 구성 file 코난(김대우) 2023.08.18 78
2246 SQL강좌: 4-6. 데이터베이스의 데이터와 로그 코난(김대우) 2023.08.18 75
2245 SQL강좌: 4-5. 데이터베이스 삭제 file 코난(김대우) 2023.08.18 45
2244 SQL강좌: 4-4. 데이터베이스 옵션 file 코난(김대우) 2023.08.18 88
» SQL강좌: 4-3. 데이터베이스 크기조절 file 코난(김대우) 2023.08.18 64
2242 SQL강좌: 4-2. 데이터베이스 생성 file 코난(김대우) 2023.08.18 78
2241 SQL강좌: 4-1. SQL Server 데이터베이스와 데이터베이스 개체의 이해 file 코난(김대우) 2023.08.18 130
2240 SQL강좌: 3-4. TRUNCATE TABLE / 트랜잭션 수행 [1] 코난(김대우) 2023.08.18 91
2239 SQL강좌: 3-3. DELETE를 이용한 로우 삭제 코난(김대우) 2023.08.18 69
2238 SQL강좌: 3-2. UPDATE - 데이터 수정 코난(김대우) 2023.08.18 85
2237 SQL강좌: 3-1. 데이터 삽입, 삭제, 수정 - INSERT를 이용한 행 삽입 코난(김대우) 2023.08.18 94
2236 SQL강좌: 2-14. SQL 쿼리 자동생성 - 쿼리 디자이너 file 코난(김대우) 2023.08.18 102
2235 SQL강좌: 2-13. SELECT 결과셋을 XML, JSON 형식으로 출력 코난(김대우) 2023.08.18 87





XE Login