오류 9002: SQL Server AVAILABILITY_REPLICA 오류 메시지로 인해 데이터베이스의 트랜잭션 로그가 가득 찼습니다.

  • 아티클

이 문서에서는 트랜잭션 로그가 커지거나 SQL Server 공간이 부족할 때 발생하는 9002 오류를 resolve 수 있습니다.

원래 제품 버전: SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012
원래 KB 번호: 2922898

증상

다음과 같은 경우를 생각해볼 수 있습니다.

  • Microsoft SQL Server 2012 이상 버전이 서버에 설치되어 있습니다.
  • SQL Server instance Always On 가용성 그룹 환경의 기본 복제본(replica).
  • 트랜잭션 로그 파일에 대한 자동 증가 옵션은 SQL Server 설정됩니다.

이 시나리오에서는 트랜잭션 로그가 커지고 디스크 공간이 부족해지거나 주 복제본(replica) 트랜잭션 로그에 대해 설정된 MaxSize 옵션을 초과할 수 있으며 다음과 유사한 오류 메시지가 표시됩니다.

오류: 9002, 심각도: 17, 상태: 9. 데이터베이스 '%.*ls'에 대한 트랜잭션 로그가 'AVAILABILITY_REPLICA'로 인해 가득 찼습니다.

원인

이는 주 복제본(replica) 기록된 변경 내용이 보조 복제본(replica) 아직 강화되지 않은 경우에 발생합니다. Always On 환경의 데이터 동기화 프로세스에 대한 자세한 내용은 데이터 동기화 프로세스를 참조하세요.

문제 해결

가용성 데이터베이스 및 의 로그 증가로 이어질 수 있는 두 가지 시나리오가 'AVAILABILITY_REPLICA' log_reuse_wait_desc있습니다.

  • 시나리오 1: 기록된 변경 내용을 보조로 배달하는 대기 시간

    트랜잭션이 기본 복제본(replica) 데이터를 변경하는 경우 이러한 변경 내용은 로그 레코드 블록으로 캡슐화되고 이러한 기록된 블록은 보조 복제본(replica) 데이터베이스 로그 파일로 전달되고 강화됩니다. 주 복제본(replica) 해당 로그 블록이 모든 보조 복제본의 해당 데이터베이스 로그 파일로 전달되고 강화될 때까지 자체 로그 파일의 로그 블록을 덮어쓸 수 없습니다. 가용성 그룹의 복제본(replica) 이러한 블록의 배달 또는 강화가 지연되면 주 복제본(replica) 데이터베이스에서 기록된 변경 내용이 잘리지 않고 로그 파일 사용량이 증가합니다.

    자세한 내용은 네트워크 대기 시간이 높거나 네트워크 처리량이 낮으면 기본 복제본(replica) 로그 빌드를 참조하세요.

  • 시나리오 2: 다시 실행 대기 시간

    보조 데이터베이스 로그 파일로 강화되면 보조 복제본(replica) instance 전용 다시 실행 스레드가 포함된 로그 레코드를 해당 데이터 파일에 적용합니다. 기본 복제본(replica) 모든 보조 복제본의 모든 다시 실행 스레드가 포함된 로그 레코드를 적용할 때까지 자체 로그 파일의 로그 블록을 덮어쓸 수 없습니다.

    보조 복제본(replica) 대한 다시 실행 작업이 해당 보조 복제본(replica) 로그 블록이 강화되는 속도를 따라갈 수 없는 경우 기본 복제본(replica) 로그 증가로 이어집니다. 기본 복제본(replica) 모든 보조 복제본(replica) 다시 실행 스레드가 적용된 지점까지만 자체 트랜잭션 로그를 잘라내고 다시 사용할 수 있습니다. 두 개 이상의 보조 데이터베이스가 있는 경우 여러 보조 데이터베이스에서 동적 관리 뷰의 sys.dm_hadr_database_replica_states 열을 비교하여 truncation_lsn 로그 잘림을 가장 지연시키는 보조 데이터베이스를 식별합니다.

    Always On 대시보드 및 sys.dm_hadr_database_replica_states 동적 관리 뷰를 사용하여 로그 보내기 큐 및 다시 실행 큐를 모니터링할 수 있습니다. 일부 주요 필드는 다음과 같습니다.

    필드 설명
    log_send_queue_size 보조 복제본(replica) 도착하지 않은 로그 레코드의 양
    log_send_rate 로그 레코드가 보조 데이터베이스로 전송되는 속도입니다.
    redo_queue_size 아직 다시 실행되지 않은 보조 복제본(replica) 로그 파일의 로그 레코드 양(KB)입니다.
    redo_rate 지정된 보조 데이터베이스(KB)/초 단위로 로그 레코드를 다시 실행 중인 속도입니다.
    last_redone_lsn 보조 데이터베이스에서 다시 실행된 마지막 로그 레코드의 실제 로그 시퀀스 번호입니다. last_redone_lsn 는 항상 보다 last_hardened_lsn작습니다.
    last_received_lsn 이 보조 데이터베이스를 호스트하는 보조 복제본(replica) 모든 로그 블록을 받은 지점을 식별하는 로그 블록 ID입니다. 0으로 패딩된 로그 블록 ID를 반영합니다. 실제 로그 시퀀스 번호가 아닙니다.

    예를 들어 주 복제본(replica) 대해 다음 쿼리를 실행하여 가장 truncation_lsn 빠른 복제본(replica) 보고하고 주 데이터베이스가 자체 트랜잭션 로그에서 회수할 수 있는 상한입니다.

    SELECT ag.name AS [availability_group_name]
, d.name AS [database_name]
, ar.replica_server_name AS [replica_instance_name]
, drs.truncation_lsn , drs.log_send_queue_size
, drs.redo_queue_size
FROM sys.availability_groups ag
INNER JOIN sys.availability_replicas ar
    ON ar.group_id = ag.group_id
INNER JOIN sys.dm_hadr_database_replica_states drs
    ON drs.replica_id = ar.replica_id
INNER JOIN sys.databases d
    ON d.database_id = drs.database_id
WHERE drs.is_local=0
ORDER BY ag.name ASC, d.name ASC, drs.truncation_lsn ASC, ar.replica_server_name ASC

    수정 조치에는 다음이 포함될 수 있지만 다음으로 제한되지는 않습니다.

    • 보조 데이터베이스에 리소스 또는 성능 병목 현상이 없는지 확인합니다.
    • 보조에서 다시 실행 스레드가 차단되지 않았는지 확인합니다. 확장 이벤트를 lock_redo_blocked 사용하여 이 문제가 발생하는 시기와 다시 실행 스레드가 차단된 개체를 식별합니다.

해결 방법

이 문제를 발생시키는 보조 데이터베이스를 식별한 후 다음 방법 중 하나 이상을 시도하여 이 문제를 일시적으로 해결해 보세요.

  • 잘못된 보조 데이터베이스에 대한 가용성 그룹에서 데이터베이스를 꺼냅니다.

    참고

    이 방법을 사용하면 보조 데이터베이스에 대한 고가용성/재해 복구 시나리오가 손실됩니다. 나중에 가용성 그룹을 다시 설정해야 할 수 있습니다.

  • 다시 실행 스레드가 자주 차단되는 경우 복제본(replica) 의 매개 변수 SECONDARY_ROLE 를 ALLOW_CONNECTIONSNO로 변경하여 기능을 사용하지 않도록 설정합니다Readable Secondary.

    참고

    이렇게 하면 사용자가 차단의 근본 원인인 보조 복제본(replica) 데이터를 읽지 못하게 됩니다. 다시 실행 큐가 허용 가능한 크기로 떨어지면 기능을 다시 사용하도록 설정하는 것이 좋습니다.

  • 비활성화되어 있고 사용 가능한 디스크 공간이 있는 경우 자동 증가 설정을 사용하도록 설정합니다.

  • 트랜잭션 로그 파일에 도달하고 사용 가능한 디스크 공간이 있는 경우 트랜잭션 로그 파일의 MaxSize 값을 늘입니다.

  • 현재 트랜잭션 로그 파일이 시스템 최대 2TB에 도달했거나 사용 가능한 다른 볼륨에서 추가 공간을 사용할 수 있는 경우 추가 트랜잭션 로그 파일을 추가합니다.

추가 정보

적용 대상

  • SQL Server 2012 Enterprise
  • SQL Server 2014 Enterprise
  • SQL Server 2014 Business Intelligence
  • SQL Server 2014 Standard
  • SQL Server 2016 Enterprise
  • SQL Server 2016 Standard
  • SQL Server 2017 Enterprise
  • SQL Server 2017 Standard Windows