Categories
Database Backup

** Discover expert strategies for preventing and resolving MSSQL transaction log full errors (Error 9002). Learn rapid recovery techniques, VLF management, and architectural best practices for DBAs.

Microsoft SQL Server를 관리하는 데이터베이스 관리자(DBA)와 DevOps 엔지니어에게 오류 9002: ‘X’ 데이터베이스의 트랜잭션 로그가 꽉 찼습니다(The transaction log for database ‘X’ is full)만큼 즉각적인 불안감을 주는 경고는 거의 없습니다. 트랜잭션 로그가 가득 차서 더 이상 증가할 수 없게 되면 데이터베이스는 사실상 읽기 전용 상태가 됩니다. 모든 INSERT, UPDATE, DELETE 작업이 중단되고, 애플리케이션 트랜잭션이 실패하며, 운영 환경이 완전히 마비됩니다.

SQL Server 트랜잭션 로그의 기본 아키텍처를 이해하고, 근본 원인을 정확하게 진단하며, 신속한 복구 절차를 실행하는 것은 고가용성을 유지하기 위한 핵심 기술입니다. 이 종합 가이드에서는 트랜잭션 로그의 메커니즘, 긴급 상황에서 로그가 꽉 찼을 때 해결하는 방법, 그리고 재발 방지를 위한 아키텍처 모범 사례를 살펴봅니다.

SQL Server 트랜잭션 로그 아키텍처 이해

꽉 찬 트랜잭션 로그를 효과적으로 문제 해결하려면 먼저 SQL Server가 데이터를 쓰고 관리하는 방식을 이해해야 합니다.

선행 기록 로깅(Write-Ahead Logging, WAL)

SQL Server는 선행 기록 로깅(WAL) 프로토콜을 사용합니다. 데이터 수정이 발생할 때마다 변경 사항은 먼저 메모리의 트랜잭션 로그에 기록된 다음, 데이터베이스 파일(MDF/NDF)의 실제 데이터 페이지가 업데이트되기 전에 디스크의 물리적 로그 파일로 플러시됩니다. 이는 ACID(원자성, 일관성, 격리성, 지속성) 준수를 보장하며, 충돌 발생 시 SQL Server가 트랜잭션을 다시 실행(roll forward)하거나 취소(roll back)할 수 있도록 합니다.

가상 로그 파일(VLF) 및 순환 로깅

내부적으로 물리적 트랜잭션 로그 파일(LDF)은 가상 로그 파일(VLF)이라는 더 작은 논리적 세그먼트로 나뉩니다. 트랜잭션 로그는 순환 방식으로 작동합니다. 로그 레코드가 기록되면 하나의 VLF를 채우고 다음 VLF로 이동합니다.

로그가 물리적 파일의 끝에 도달하면 처음으로 돌아가 덮어쓰기를 시도합니다. 그러나 VLF가 비활성(inactive)으로 표시된 경우에만 덮어쓸 수 있습니다. 모든 VLF가 활성 상태(즉, SQL Server가 여전히 필요로 하는 로그 레코드를 포함하고 있음)라면 로그는 순환할 수 없습니다. 자동 증가가 활성화되어 있고 디스크 공간이 충분하면 물리적 파일이 커집니다. 디스크가 꽉 찼거나 자동 증가가 제한된 경우 오류 9002가 발생합니다.

로그 잘림(Truncation) vs. 로그 축소(Shrinking)

로그를 잘라내는 것이 물리적 파일 크기를 줄인다는 것은 흔한 오해입니다.
* 로그 잘림(Log Truncation): 활성 VLF를 비활성으로 표시하여 재사용 가능한 공간으로 만드는 과정입니다. 디스크의 LDF 파일 크기를 줄이지는 않습니다.
* 로그 축소(Log Shrinking): LDF 파일 크기를 물리적으로 줄여 운영 체제에 공간을 반환하는 과정입니다.

전체 복구 모델에서 로그 잘림은 트랜잭션 로그 백업이 성공적으로 완료되었을 때만 발생합니다(다른 프로세스가 로그를 활성 상태로 유지하지 않는다고 가정).

“트랜잭션 로그 꽉 참” 오류(오류 9002) 진단

로그가 꽉 찼을 때 첫 번째 단계는 무작정 디스크 공간을 추가하거나 파일을 축소하는 것이 아닙니다. 로그가 잘리지 않는 이유를 파악해야 합니다. SQL Server는 sys.databases 카탈로그 뷰를 통해 로그 재사용을 방해하는 원인을 정확히 알려주는 내장 메커니즘을 제공합니다.

다음 T-SQL 명령을 실행하여 병목 현상을 식별하십시오:

SELECT 
    name AS DatabaseName, 
    recovery_model_desc AS RecoveryModel, 
    log_reuse_wait_desc AS LogReuseWaitReason
FROM sys.databases
WHERE name = 'YourDatabaseName';

다음 명령을 사용하여 트랜잭션 로그의 현재 공간 사용량을 확인할 수도 있습니다:

DBCC SQLPERF(LOGSPACE);

일반적인 log_reuse_wait_desc 상태

  1. LOG_BACKUP: 데이터베이스가 전체 또는 대량 로그 복구 모델이며, 최근에 트랜잭션 로그 백업을 수행하지 않았습니다. 가장 흔한 원인입니다.
  2. ACTIVE_TRANSACTION: 장기 실행 트랜잭션(예: 대규모 인덱스 재구축 또는 커밋되지 않은 잊혀진 트랜잭션)이 로그를 활성 상태로 유지하고 있습니다.
  3. REPLICATION / CDC: 트랜잭션 복제 또는 변경 데이터 캡처(CDC)가 활성화되어 있으며, 로그 판독기 에이전트가 아직 트랜잭션을 처리하지 않았습니다.
  4. AVAILABILITY_REPLICA: AlwaysOn 가용성 그룹에서 보조 복제본이 연결 해제되었거나 동기화 속도가 너무 느려, 주 복제본이 보조 복제본에 로그 레코드가 기록될 때까지 로그를 유지하도록 강제하고 있습니다.

신속한 복구 전략: 운영 환경에서 문제 해결

반환된 log_reuse_wait_desc에 따라 긴급 대응 방법이 달라집니다. 가장 일반적인 시나리오에 대한 신속한 복구 전략은 다음과 같습니다.

시나리오 1: 로그 백업 누락 또는 실패 (LOG_BACKUP)

대기 유형이 LOG_BACKUP인 경우 해결책은 간단합니다. 트랜잭션 로그를 백업해야 합니다.

BACKUP LOG [YourDatabaseName] 
TO DISK = 'N:BackupsYourDatabaseName_EmergencyLog.trn' 
WITH COMPRESSION, STATS = 10;

백업이 완료되면 비활성 VLF가 잘리고 SQL Server가 정상 작동을 재개합니다. 백업 드라이브가 꽉 찼다면 임시 네트워크 공유나 null 장치로 백업해야 할 수도 있습니다(데이터베이스를 쉽게 재현할 수 없는 경우 로그 체인이 끊어지므로 권장하지 않음):

-- 경고: 이 작업은 로그 체인을 끊고 특정 시점 복구를 불가능하게 만듭니다.
-- 절대적으로 필요한 경우에만 사용하고 즉시 전체 백업을 수행하십시오.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

시나리오 2: 장기 실행 활성 트랜잭션 (ACTIVE_TRANSACTION)

단일 트랜잭션이 몇 시간 동안 실행 중이면 전체 기간 동안 로그 잘림을 방해합니다. 먼저 문제를 일으키는 트랜잭션을 식별하십시오:

DBCC OPENTRAN('YourDatabaseName');

이 명령은 가장 오래된 활성 트랜잭션과 해당 SPID(서버 프로세스 ID)를 반환합니다. 동적 관리 뷰(DMV)를 쿼리하여 해당 SPID가 수행 중인 작업에 대한 자세한 내용을 확인할 수 있습니다:

SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    r.start_time,
    r.status,
    r.command,
    t.text AS QueryText
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.session_id = <SPID_FROM_DBCC_OPENTRAN>;

트랜잭션이 잘못된 쿼리이거나 중단된 프로세스인 경우 로그를 해제하기 위해 종료해야 할 수 있습니다.

KILL <SPID>;

참고: 대규모 트랜잭션을 강제 종료하면 롤백이 트리거되며, 이는 상당한 시간이 소요될 수 있고 일시적으로 추가 로그 활동을 생성합니다. 롤백 중에 SQL Server 서비스를 다시 시작하지 마십시오. 그렇지 않으면 다시 시작할 때 데이터베이스가 복구 모드로 진입합니다.

시나리오 3: 긴급 공간 할당 (디스크 100% 사용)

LDF 파일이 전체 드라이브를 소비한 경우, SQL Server가 백업 이벤트 자체를 기록하기 위해 약간의 로그 공간을 필요로 하므로 백업조차 실행할 수 없습니다. 이 시나리오에서는 사용 가능한 공간이 있는 다른 드라이브에 보조 로그 파일을 추가해야 합니다.

ALTER DATABASE [YourDatabaseName]
ADD LOG FILE 
(
    NAME = N'YourDatabaseName_Log2',
    FILENAME = N'E:TempLogsYourDatabaseName_Log2.ldf',
    SIZE = 5GB,
    MAXSIZE = 50GB,
    FILEGROWTH = 1GB
);

이렇게 하면 즉시 SQL Server에 숨통이 트입니다. 데이터베이스가 온라인 상태가 되면 트랜잭션 로그 백업을 수행하고, 보조 로그 파일을 비운 다음 제거하십시오:

-- 1. 로그를 자르기 위해 로그 백업 수행
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. 임시 로그 파일 비우기
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. 임시 로그 파일 제거
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

트랜잭션 로그 예방 및 관리를 위한 모범 사례

사후 대응식 문제 해결은 스트레스가 많고 SLA에 영향을 미칩니다. 엔터프라이즈 데이터베이스 안정성을 위해서는 사전 예방적 아키텍처 및 운영 모범 사례를 구현하는 것이 필수적입니다.

1. 강력하고 자동화된 백업 전략 구현

데이터베이스가 전체 복구 모델인 경우 빈번한 트랜잭션 로그 백업은 필수입니다. RPO(복구 목표 시점)와 트랜잭션 볼륨에 따라 로그 백업은 5~15분마다 수행되어야 합니다.

CloudSave와 같은 엔터프라이즈 백업 솔루션은 이 과정을 크게 단순화합니다. VDI(가상 장치 인터페이스)를 통해 SQL Server와 직접 통합함으로써, CloudSave는 DBA가 정책 기반의 고빈도 트랜잭션 로그 백업을 구성할 수 있도록 지원합니다. 이를 통해 복잡한 사용자 지정 SQL 에이전트 작업 없이도 로그가 지속적으로 잘리고, 안전하게 암호화되며, 오프사이트 또는 변경 불가능한 클라우드 스토리지에 저장되어 LOG_BACKUP 대기 상태를 방지합니다.

2. 트랜잭션 로그 크기 최적화 및 VLF 관리

트랜잭션 로그 크기를 관리하기 위해 자동 증가에 의존하는 것은 위험한 안티 패턴입니다. 자동 증가 작업은 비용이 많이 들며 디스크가 0으로 초기화되는 동안 트랜잭션 처리를 일시 중지시킵니다(로그 파일에는 적용되지 않는 즉시 파일 초기화가 활성화된 경우 제외).

또한, 빈번하고 작은 자동 증가(예: 한 번에 10% 또는 50MB씩 증가)는 VLF 조각화를 초래합니다. 수천 개의 작은 VLF가 있는 트랜잭션 로그는 데이터베이스 시작 시간, 백업 성능 및 복제 지연 시간을 심각하게 저하시킵니다.

  • 로그 크기 사전 설정: 가장 큰 유지 관리 작업(예: 인덱스 재구축)을 분석하고 증가 없이 수용할 수 있도록 LDF 파일 크기를 미리 설정하십시오.
  • 고정 자동 증가 설정: 자동 증가를 백분율이 아닌 고정 크기(예: 1GB 또는 5GB)로 변경하여 VLF가 적절한 크기로 생성되도록 하십시오.

다음 쿼리를 사용하여 VLF 개수를 확인할 수 있습니다(SQL Server 2017 이상):

SELECT 
    db_name(database_id) AS DatabaseName,
    COUNT(vlf_sequence_number) AS VLF_Count
FROM sys.dm_db_log_info(DB_ID('YourDatabaseName'));

VLF 개수가 500개를 넘는다면, 한가한 시간을 기다려 로그를 최소 크기로 축소한 다음, 큰 단위로 필요한 크기만큼 수동으로 다시 늘리는 것을 고려하십시오.

3. 인덱스 유지 관리 작업 최적화

인덱스 재구축은 대량 로그 복구 모델에서도(인덱스 유형에 따라) 완전히 기록되는 작업입니다. 500GB 인덱스를 재구축하면 최소 500GB의 트랜잭션 로그 레코드가 생성됩니다.

유지 관리 중 로그 팽창을 완화하려면:
* 인덱스 재구축 시 SORT_IN_TEMPDB = ON을 사용하십시오. 정렬 단계를 TempDB로 오프로드하여 사용자 데이터베이스 트랜잭션 로그의 부담을 줄입니다.
* 가능한 경우 인덱스 재구축에서 인덱스 재구성으로 전환하십시오. 재구성은 로그 효율성이 더 높으며 전체 작업을 롤백하지 않고도 중단할 수 있습니다.
* 대규모 DELETE 또는 UPDATE 작업을 일괄 처리하십시오. 한 트랜잭션에서 1,000만 행을 삭제하는 대신 50,000개 단위로 삭제하고, 배치 사이에 커밋하여 로그 백업이 로그를 잘라낼 수 있도록 하십시오.

4. 고가용성 및 복제 토폴로지 모니터링

AlwaysOn 가용성 그룹에서 주 복제본은 모든 동기 및 비동기 보조 복제본에 로그 레코드가 기록될 때까지 로그를 자를 수 없습니다.

보조 복제본이 오프라인 상태가 되거나 네트워크 대역폭이 주 복제본의 트랜잭션 생성 속도를 따라가지 못하면 주 복제본의 전송 큐가 커지고 로그가 꽉 차게 됩니다(AVAILABILITY_REPLICA 대기 유형).

SQLServer:Replica > Log Send Queue 성능 카운터에 대한 강력한 모니터링을 구현하십시오. 보조 복제본이 영구적으로 손실된 경우 가용성 그룹에서 제거하거나 데이터 이동을 일시 중단하여 주 로그가 잘릴 수 있도록 해야 합니다.

결론

트랜잭션 로그가 꽉 차는 것은 데이터베이스 관리자에게 통과의례와 같지만, 장기적인 가동 중단으로 이어질 필요는 없습니다. 선행 기록 로깅과 VLF의 메커니즘을 이해하면 sys.databases를 사용하여 근본 원인을 빠르게 진단하고 올바른 신속 복구 전략을 적용할 수 있습니다.

장기적인 안정성은 사후 대응식 수정에서 벗어나는 데 달려 있습니다. 로그 파일을 미리 설정하고, 유지 관리 루틴을 최적화하며, CloudSave와 같은 엔터프라이즈급 백업 플랫폼을 활용하여 엄격하고 자동화된 로그 백업 일정을 시행하면 트랜잭션 로그를 건강하게 유지하고 고처리량 운영 워크로드를 지원할 준비를 갖출 수 있습니다.

카테고리