혹시 데이터베이스 용량 때문에 고민해본 적이 있을까?
서비스를 운영하다 보면 자연스레 데이터가 계속 쌓이면서 데이터베이스 용량이 증가하고
어느샌가 알게 모르게 디스크 공간이 부족해지는 경험을 하게 된다.
특히 MSSQL의 로그 파일(*.ldf)은 크기가 급증하는 경우가 많아 더욱 관리가 까다롭다.
이번 글에서는 MSSQL DB 용량과 로그 사이즈를 효과적으로 줄이는 방법을 설명하고자 한다.
데이터베이스의 용량은 늘어나면 늘어났지, 줄어들지 않는다
이게 무슨 말일까?
데이터베이스에서는 데이터를 삭제해도 실제 디스크 공간에서는 용량이 확보되지 않는다.
이는 MSSQL은 데이터베이스의 일관성 유지를 위해서 데이터를 삭제할 때
실제 데이터 페이지에서 데이터를 즉시 제거하는 대신, 해당 공간을 ‘사용 가능’ 상태로 표시하기 때문이다.
즉, 새로운 데이터가 입력될 때까지 해당 공간은 비어 있지만,
디스크 공간에 용량이 확보되지 않고 해당 공간은 데이터베이스의 예약된 상태로 남아있게 되는 것이다.
데이터베이스 용량, 왜 줄여야 할까?
데이터베이스 용량 관리는 단순히 디스크 공간 확보를 목적으로 하는 것이 아닌 서비스 성능과도 직결되는 중요한 문제이기 때문이다.
용량이 비대해지면 쿼리 실행 속도가 느려지고, 백업 시간도 오래 걸리며, 심지어 심할 경우에는 시스템 다운으로 이어질 수도 있다.
데이터베이스 용량을 관리하지 않고 그대로 방치해둔다면 발생할 수 있는 문제점은 다음과 같다.
- 쿼리 성능 저하로 인한 서비스 속도 저하
- 백업 및 복구 시간 증가
- 디스크 공간 부족으로 인한 시스템 불안정 및 장애 발생 위험 증가
- 불필요한 저장 공간 비용 발생
따라서 정기적인 데이터베이스 용량 관리를 통해 최적의 성능을 유지하고 잠재적인 문제를 예방하는 것이 매우 중요하다.
DB 파일 용량 축소하기 Step by Step
1단계: 불필요한 데이터 삭제하기
가장 먼저 할 일은 바로 불필요한 데이터를 삭제하여 용량을 확보하는 것이다.
오래된 데이터, 임시 테이블, 중복 데이터 등을 식별하여 필요 없는 데이터를 삭제하자.
혹시 모를 경우를 대비해서 삭제 전에 반드시 데이터 백업을 진행해두자.
-
DELETE문을 사용하여 특정 조건에 맞는 필요 없는 데이터 삭제 -
TRUNCATE TABLE명령어를 사용하여 필요 없는 테이블 전체 데이터 삭제 (단, 복구 불가능하므로 주의!)
2단계: 데이터베이스 파일(.mdf) 용량 축소하기
우선 데이터베이스의 이름과 데이터 파일의 용량을 확인해보자.
-- 데이터베이스의 데이터 파일 및 로그 파일 용량 확인
SELECT
name AS [파일명],
size * 8 / 1024 AS [크기(MB)],
type_desc AS [유형]
FROM sys.master_files이제 데이터를 삭제한 후, 남아 있는 예약된 공간을 실제 디스크 용량으로 반환할 수 있도록 해주어야 한다.
MS-SQL의 SSMS에서 GUI를 통해서도 할 수 있지만, 이 글에서는 쿼리를 이용하여 작업을 진행하겠다.
(개인적으로 쿼리로 하는 것이 여러 데이터베이스의 축소 작업을 진행 할 때 여러모로 편리한 것 같다.)
DBCC SHRINKDATABASE(데이터베이스명)
/* [예시]
데이터베이스의 이름이 test일 경우
->DBCC SHRINKDATABASE(test)
*/✅ DBCC SHRINKDATABASE 구문
SQL Server에서 데이터베이스의 파일(데이터, 로그)을 축소하여 디스크 공간을 회수하는 데 사용하는 명령어이다.
데이터 파일이 불필요하게 크거나 데이터베이스 공간이 과도하게 사용된 경우, 축소 작업을 통해 사용하지 않는 공간을 반환할 수 있다.
첫 번째 파라미터로 축소하려는 데이터베이스의 이름을 입력하며,
두 번째 파라미터에는 선택사항으로, 데이터베이스 파일 내에서 남겨둘 여유 공간의 비율을 입력한다.
두 번째 파라미터에 만약 ’10’의 값을 입력할 경우에는 10% 여유 공간을 남기고, 입력하지 않으면 여유 공간 없이 축소된다.
3단계: 로그 파일(.ldf) 크기 줄이기
로그 파일의 용량을 축소하기 위해서는 우선 복구 모델의 유형을 알아야 한다.
기본적으로 전체 복구 모델(Full Recovery Model)로 설정된 경우에는 백업을 하지 않으면 로그 파일의 축소 작업을 진행할 수 없다.
또한 무엇보다도 사전 지식 없이 단순히 로그 파일의 용량 축소를 위해 로그의 내용을 날려버리는 것은 매우 위험하기 때문이다.
✅ 복구 모델(Recovery Model)이란?
데이터베이스의 트랜잭션 로그가 어떻게 관리되고
장애 발생 시 데이터 복구가 어떻게 이루어지는지를 결정하여
데이터 손실 위험과 로그 관리 방식을 제어하는 역할을 한다.
로그 파일 축소 시에는 크게 두 가지의 복구 모델 유형을 이용한다.
전체 복구 모델은 높은 수준의 데이터 무결성과 복구가 필요한 시스템(금융 거래 시스템 등)에서 사용되며,
단순 복구 모델은 복구가 필요하지 않은 개발 환경이나 데이터 손실이 크게 중요하지 않는 시스템에서 주로 사용된다.
- 전체 복구 모델 (Full Recovery Model)
- 모든 데이터 변경 작업(트랜잭션)이 트랜잭션 로그에 기록되며, 로그 백업을 수행하기 전까지 로그가 삭제되지 않음을 의미함
- 로그 백업을 주기적으로 실행해야 하며, 로그 파일 크기가 크게 증가할 수 있음
- 단순 복구 모델 (Simple Recovery Model)
- 트랜잭션 로그가 자동으로 관리되며, 트랜잭션이 완료되면 로그가 자동으로 제거됨
- 로그 백업이 불가능하며, 장애 발생 시 마지막 백업 시점까지만 복구할 수 있음
- 로그 파일 크기가 자동으로 줄어들기 때문에 로그 관리가 간편하지만, 특정 시점으로 복구할 수 없음
우선 내가 진행하는 데이터베이스의 복구 모델이 어떻게 설정되어 있는지 확인해보자.
-- 현재 세션에 접속되어 있는 데이터베이스의 복구 모델 확인하기
SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = DB_NAME()
-- 데이터베이스 전체 복구 모델 확인하기
SELECT name, recovery_model_desc
FROM sys.databases
그리고 나서 로그 파일의 축소를 어떤 모델에서 진행할 것인지 선택한다.
Case1. 전체 복구 모델의 로그 파일 축소하기
/* 전체 복구 모델의 경우 */
-- 로그 백업
BACKUP DATABASE [데이터베이스_이름] TO DISK = '경로\백업파일.bak';
-- 로그 파일 축소 : 5메가로 축소하기
DBCC SHRINKFILE (N'로그파일이름', 5); ✅ DBCC SHRINKFILE 구문
SQL Server에서 특정 데이터 파일이나 트랜잭션 로그 파일의 크기를 축소하여 사용하지 않는 디스크 공간을 회수하는 데 사용하는 명령어이다.
DBCC SHRINKDATABASE가 데이터베이스 전체에 적용되는 반면,DBCC SHRINKFILE은 특정 파일에만 적용된다.
첫 번째 파라미터에는 축소하려는 파일의 이름을 입력하고,
두 번째 파라미터는 축소하려는 목표 파일의 크기(MB 단위)를 입력한다.
Case2. 단순 복구 모델의 로그 파일 축소하기
SQL Server에서는 일반적으로 데이터베이스가 전체 복구 모델인 경우가 많다.
때문에 경우에 따라서 복구 모델을 변경하여 로그 파일 축소 작업을 진행해야 할 수도 있다.
/* 전체 복구 모델이지만 로그 백업이 불필요한 경우 */
-- 로그를 아예 날려버리기 위해 데이터베이스의 복구 모델을 간단 복구 모델로 변경
ALTER DATABASE [데이터베이스명] SET RECOVERY SIMPLE
-- 로그 파일 축소
DBCC SHRINKFILE ('로그파일명', 5); -- 5MB로 축소
-- 복구 모델을 다시 전체 복구로 변경
ALTER DATABASE [데이터베이스명] SET RECOVERY FULL
/* 단순 복구 모델인 경우 */
-- 로그 파일 축소
DBCC SHRINKFILE ('로그파일명', 5); -- 5MB로 축소🗝️ DBCC SHRINKDATABASE와 DBCC SHRINKFILE의 차이는?
DBCC SHRINKDATABASE는 데이터베이스 전체에 대해 축소를 수행하며, 데이터 파일과 로그 파일 모두를 축소한다.
DBCC SHRINKFILE은 특정 파일(데이터 파일 또는 트랜잭션 로그 파일)에 대해서만 축소를 수행한다.
DB 축소 예시
아래의 쿼리는 실제로 사내 개발 서버의 데이터베이스를 축소하는 작업을 할 때 사용된 쿼리이다.
-- 현재 데이터베이스의 파일명을 모를 경우의 용량 확인
use xxx
exec sp_helpfile -- xxx 데이터베이스 데이터 파일 용량 415기가 / 로그 용량 91기가
-- DB 작업 축소 전 용량 확인
use xxx
SELECT name AS [파일명], size * 8 / 1024 AS [크기(MB)]
FROM sys.master_files
WHERE type_desc = 'ROWS' and name = 'xxx' -- 데이터 파일 축소 전 : 415816 MB
use xxx
SELECT name AS [파일명], size * 8 / 1024 AS [크기(MB)]
FROM sys.master_files
WHERE type_desc = 'LOG' and name = 'xxx_log' -- 로그 파일 축소 전 : 91528 MB
-- 필요 없는 데이터 삭제
delete from ~
truncate table ~
-- 데이터베이스 용량 축소
DBCC SHRINKDATABASE(xxx)
-- 로그를 아예 날려버리기 위해 간단 복구 모델로 변경
ALTER DATABASE xxx SET RECOVERY SIMPLE
-- 로그 파일 축소
DBCC SHRINKFILE (xxx_log, 5); -- 5MB로 축소
-- 복구 모델을 다시 전체 복구로 변경
ALTER DATABASE xxx SET RECOVERY FULL
-- DB 작업 축소 후 용량 확인
use xxx
SELECT name AS [파일명], size * 8 / 1024 AS [크기(MB)]
FROM sys.master_files
WHERE type_desc = 'ROWS' and name = 'xxx' -- 데이터 파일 축소 전 : 92547MB
use xxx
SELECT name AS [파일명], size * 8 / 1024 AS [크기(MB)]
FROM sys.master_files
WHERE type_desc = 'LOG' and name = 'xxx_log' -- 축소 후 : 5 MB