SQL Server 트랜잭션 로그 꽉 참 문제 해결 및 예방

문제 상황

최근 웹 애플리케이션에서 메뉴 접속 기록을 데이터베이스에 저장하는 과정에서 아래와 같은 SQL Server 오류가 발생했다. 이로 인해 서비스가 정상적으로 동작하지 않는 문제가 발생했다.

(참고로 DB서버의 OS는 Window 이다)

[S0002][9002] 데이터베이스 '[YourDatabaseName]'의 트랜잭션 로그가 'LOG_BACKUP'(으)로 인해 꽉 찼습니다.

 

처음 보는 상황이였어서 당황했지만 해결방법은 비교적 간단했다.

 

 

문제 발생 원인 분석

확인해본 결과 문제의 원인은 다음과 같이 세 가지라고 생각되었다.

1.  전체(Full) 복구 모델 사용

      SQL Server의 전체(Full) 복구 모델은 모든 트랜잭션을 로그에 기록하므로, 데이터베이스를 특정 시점으로 복구할 수 있는 장점이 있다. 하지만 이 모델에서는 주기적인 로그 백업을 통해 로그 파일을 비워주지 않으면 파일 크기가 계속해서 증가한다. 이번 문제의 경우 정기적인 로그 백업이 수행되지 않아 로그가 누적되었다.

2.  로그 파일 크기 제한(MAXSIZE)

      해당 데이터베이스의 로그 파일(`[YourDatabaseName]_log.ldf`)은 최대 크기(MAXSIZE)가 2GB로 제한되어 있었다. 자동 증가(FILEGROWTH) 옵션이 10%로 설정되어 있었지만, 최대 크기인 2GB에 도달하자 더 이상 파일이 확장되지 못하고 오류가 발생했다.

3.  모니터링 부재

      트랜잭션 로그 파일의 사용량이나 서버의 디스크 사용량에 대한 모니터링 시스템이 없어서 문제가 발생하기 전에 미리 대응하기 어려웠다.

 

 

 

해결 과정

문제 해결을 위해 다음과 같은 조치를 단계적으로 수행했다.

1.  트랜잭션 로그 즉시 백업 (긴급 조치)
    우선 서비스가 정상동작 되어야 하므로, 가장 먼저 누적된 로그를 비워 공간을 확보하기 위해 즉시 로그 백업을 실행했다.

BACKUP LOG [YourDatabaseName]
    TO DISK = 'D:\Backup\[YourDatabaseName]_log_backup.trn';

    위 명령어를 실행하자 로그 공간이 확보되어 애플리케이션의 데이터 저장이 정상적으로 이루어지기 시작했다.

2.  로그 파일 최대 크기(MAXSIZE) 제한 해제
    로그 파일이 더 이상 크기 제한으로 인해 문제를 일으키지 않도록 최대 크기 설정을 '제한 없음(UNLIMITED)'으로 변경하고, 자동 증가 단위를 고정 크기(512MB)로 조정했다.

ALTER DATABASE [YourDatabaseName]
    MODIFY FILE (
        NAME = '[YourDatabaseName]_log',
        MAXSIZE = UNLIMITED,
        FILEGROWTH = 512MB
    );

3.  로그 파일 크기 축소 (필요시 수행. 진행하지는 않음)
    필요 이상으로 커진 로그 파일의 물리적인 크기를 줄이고 싶을 때는 아래 명령어를 사용할 수 있다. 단, 이 작업은 데이터베이스 성능에 영향을 줄 수 있으므로 신중하게 사용해야 한다고 한다.

-- 로그 파일을 1GB로 축소
    DBCC SHRINKFILE ([YourDatabaseName]_log, 1024);

 

예방 조치 계획

 

이미 발생한 문제는 어쩔 수 없지만, 차후 동일한 문제의 재발을 방지하기 위해 다음과 같은 예방 조치를 계획하고 실행하는 것이 중요하다고 생각한다. 이번에 장애가 난 서비스는 테스트용 서비스라 다행이지만 차후 운영환경에서 이런일이 발생하면 안될 것이다. 아래는 생각해본 예방 조치를 나열한 것이다.

1.  주기적인 트랜잭션 로그 백업 자동화

      SQL Server Agent Job이나 Windows 작업 스케줄러를 이용해 로그 백업을 자동화한다.

(백업 주기는 시스템의 트랜잭션 양에 따라 30분에서 1시간 사이로 설정하는 것을 권장)

2.  로그 파일 자동 증가 설정 최적화

      파일 자동 증가(FILEGROWTH) 단위를 백분율(%) 대신 고정된 크기(MB 또는 GB)로 설정하면, 파일이 성장할 때의 부하를 예측하고 관리하기 용이하다.

3.  모니터링 시스템 구축

      정기적으로 트랜잭션 로그 사용률을 확인하고, 특정 임계값(예: 80%)을 초과할 경우 관리자에게 알림을 보내는 시스템을 구축해야 한다. 아래 쿼리를 활용하여 모니터링 시스템을 구축할 수 있을 것이라고 생각한다(숙제).

 -- 참고
 
 -- 데이터베이스별 로그 공간 사용 현황 확인
 DBCC SQLPERF(LOGSPACE);

 -- 현재 활성화된(열린) 트랜잭션 확인
 DBCC OPENTRAN([YourDatabaseName]);

   
4.  서버 디스크 공간 모니터링

      로그 파일이 저장되는 서버의 전체 디스크 공간을 주기적으로 모니터링하여, 디스크 공간 부족으로 인한 장애를 사전에 예방해야 한다.

 

결론

이번 장애를 통해 트랜잭션 로그 관리의 중요성을 다시 한번 확인했다. 데이터베이스의 복구 모델을 정확히 이해하고 그에 맞게 정기적인 로그 백업 정책을 수립하는 것이 안정적인 서비스 운영의 핵심이라는 것을 다시한번 상기하게 되었다.

파일 크기 설정을 최적화하는것도 중요하지만, 차후에는 지속적인 모니터링 시스템을 갖추어 장애에 신속하게 대응할 수 있는 환경을 구성해보려고 한다.