[MSSQL] 트랜잭션 로그 백업

저는 전문DBA가 아닙니다.. 하지만 .. 해야할 일을 해야할 때가 있습니다. (ㅜㅜ)

트랜잭션 로그 사용률이 90%가 넘어갔다.. 

그도 그럴것이. 별다른 관리 포인트가 없었기 때문입니다.

기존에 응급대처로 SHRINKFILE 을 사용해서 로그를 축소시킨 경험이 있습니다. 하지만 추후 알아보니, 이는 해결 방법이 아니었습니다. (:뭐, 필요할 때가 있겠지만, 주기적인 관리 포인트가 아닌것은 공식문서로 확인했음.)

 

https://learn.microsoft.com/ko-kr/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver17

 

그리고 사용 가능한 공간이 부족하면 축소 작업에서 파일 크기를 더 이상 줄일 수 없게 되는데, 그 이유는 다음과 같습니다.

일반적으로 축소되지 않는 로그 파일은 주로,
정기적인 트랜잭션 로그 백업으로 인해 잘리지 못한 로그 파일에서 비롯된 결과입니다. 
로그를 자르려면 트랜잭션 로그를 백업한 다음 DBCC SHRINKFILE 작업을 다시 실행합니다.

 

즉, "로그는 먼저 로그 백업으로 재사용 가능한 상태로 만든 뒤 필요에 의해 SHRINK 한다."

저는 구글링과 AI 를 통해 결론을 위와 같이 내린 후, 트랜잭션 로그 백업을 진행하게 되었습니다.

 

[ SHRINKFILE ]

목적: 로그/데이터 파일의 물리적 크기를 줄여서 디스크 공간을 되찾을 때

SHRINKFILE 자주 쓰면 단편화·재증가 반복으로 부담. 필요할 때만 사용

[ BACKUP ]

목적: 복구 가능성 확보 + (FULL 모델에서) 로그 잘림으로 로그 재사용

 

* 트랜잭션 로그 : 각 트랜잭션에 의해 적용된 모든 트랜잭션 및 데이터베이스 수정 내용을 기록하는 로그

* 트랜잭션 로그 백업 조건 : 데이터베이스 복구 모델이 전체(Full) 또는 대량 로그(Bulk-Logged)여야 합니다. 또한, BACKUP LOG 권한이 없는 경우 실행할 수 없습니다.

 

[ 사전 체크 사항 ]

• 백업하려는 트랜잭션 로그가 속한 데이터베이스의 이름
• 트랜잭션 로그 백업이 기록되는 백업 디바이스
• 현재 트랜잭션 로그 사용률
• 권한 

방법 : SSMS(SQL Server Management Studio) 를 사용하여 ui로 처리할 수도 있고,Transact-SQL 사용으로 트랜잭션 로그를 백업할 수 있으나, 이 글에서는 Transact-SQL 로 백업을 실행하였습니다. 

아래 작성된 MANUAL 을 자신의 DB에 맞게 수정하여 사용하면 됩니다.

---------------------------------------------

-- 트랜잭션 로그 백업 MANUAL

---------------------------------------------

-- (1) 현재 DB 로그 파일 크기 및 사용률 조회

DBCC SQLPERF(LOGSPACE);

 

/* 복구모델 조회 */

SELECT name AS db_name,
recovery_model_desc
FROM sys.databases
WHERE name = 'YOURDB';

 

-- (2) 현재 DB 로그 파일 상세 (경로, 할당 크기)

SELECT name AS [파일명],
physical_name AS [경로],
size * 8 / 1024 AS [할당_MB],
type_desc
FROM sys.database_files
WHERE type = 1; -- LOG

 

-- (3) 트랜잭션 로그 백업 

-- 해당 경로에 폴더생성 또는 존재하는지 확인 후, 백업을 수행할 수 있는 용량이 충분한지 체크합니다.

-- 이 작업을 정기적으로 실행할 수 있게 스케줄러를 등록하여 관리하는 방법이 권장됨.

BACKUP LOG [YOURDB]
TO DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\LogBackup\YOURDB_log_20260209_130122.trn' -- 파일명의 시간 변경
WITH INIT, COMPRESSION;

 

-- (4) 백업 후 DB 로그 사용률이 낮아졌는지 확인

DBCC SQLPERF(LOGSPACE);

 

성공 message

파일 1에서 데이터베이스 'YOURDB', 파일 ' YOURDB_log'에 대해 4932907개의 페이지를 처리했습니다 .

BACKUP LOG이(가) 4932907개의 페이지를 158.357초 동안 처리했습니다(243.363MB/초).

완료 시간: 2026-02-09T14:00:46.3416061+09:00

 

90% to 0.3%

 

BACKUP LOG 결과

1. 로그 재사용 가능

로그는 잘려야 재사용이 된다. 로그 백업을 하지 않으면 “백업한 구간”이 없어서, SQL Server가 로그를 잘라 줄 수 없음. 그 결과 로그가 계속 쌓이기만 함.

2. 서비스 장애 예방(사실 필수임)

로그가 다 차면 INSERT/UPDATE/DELETE가 실패하고, 서비스 장애로 이어짐. 로그 백업으로 잘라 주어야 재사용 공간이 생김.

3. 시점 복구 가능

백업 파일(.trn)로 안전하게 저장 -> 전체 백업 + 로그 백업으로 특정 시점까지 복구 가능. 백업을 안 하면 그만큼 복구 구간이 없음.

 

* 트랜잭션 로그 파일의 크기가 줄어들지 않았다? -> 정상입니다.

BACKUP LOG는 디스크에 있는 물리적 파일(.ldf) 크기를 줄이는 것이 아니다.

백업 전 (.ldf 파일 내부)
┌──────────┬──────────┬──────────┬──────────┐
│  VLF 1   │  VLF 2   │  VLF 3   │  VLF 4   │
│ 트랜잭션 │ 트랜잭션 │ 트랜잭션 │ 트랜잭션 │
│  기록됨  │  기록됨  │  기록됨  │  기록됨  │
└──────────┴──────────┴──────────┴──────────┘
 사용률 90%                      파일크기 10GB

로그 백업 실행 후
┌──────────┬──────────┬──────────┬──────────┐
│  VLF 1   │  VLF 2   │  VLF 3   │  VLF 4   │
│ 재사용   │ 재사용   │ 트랜잭션 │ 트랜잭션 │
│ 가능 ✓   │ 가능 ✓   │  기록됨  │  기록됨  │
└──────────┴──────────┴──────────┴──────────┘
 사용률 0.3%                      파일크기 10GB (그대로!)

• “이미 백업한 로그 구간”을 재사용 가능한 공간으로 표시(truncate) 만 함.
• 그래서 파일 안의 내용만 비우는 것이고, 파일이 차지하는 용량은 그대로이다. 하지만 그 안의 빈공간을 재사용하므로 로그가 무한히 커지지 않고, 사용률을 일정 수준으로 유지할 수 있게된다.

효과  : 내부 공간을 “재사용 가능”으로 만듦 → 사용률(%) 감소

파일 축소 = 물리적 크기 줄이기 → SHRINKFILE 같은 별도 작업 필요이 필요합니다.

 

다음에는 트랜잭션 로그 백업 주기를 정하여 정기적으로 수행하는 스케줄러를 등록할 수 있게 공부 해야겠습니다.