Azure SQL Database에서 트랜잭션 로그 오류 문제 해결

아티클
03/21/2024

트랜잭션 로그가 가득 차서 새 트랜잭션을 수락할 수 없을 때 9002 또는 40552 오류가 표시될 수 있습니다. 이러한 오류는 Azure SQL Database에서 관리하는 데이터베이스 트랜잭션 로그가 공간 임계값을 초과하여 트랜잭션을 더 이상 수락할 수 없는 경우에 발생합니다. 이러한 오류는 SQL Server에서 트랜잭션 로그가 가득 찬 문제와 비슷하지만, SQL Server, Azure SQL Database 및 Azure SQL Managed Instance에서는 해결 방법이 다릅니다.

참고 항목

이 문서에서는 Azure SQL Database를 중점적으로 다룹니다. Azure SQL Database는 안정적인 최신 버전의 Microsoft SQL Server 데이터베이스 엔진에 기반하므로 문제 해결 옵션 및 도구는 SQL Server와 다를 수 있어도 많은 콘텐츠가 비슷합니다.

Azure SQL Managed Instance에서 트랜잭션 로그 문제 해결에 대한 자세한 내용은 Azure SQL Managed Instance에서 트랜잭션 로그 오류 문제 해결을 참조하세요.

SQL Server의 트랜잭션 로그 문제 해결에 대한 자세한 내용은 꽉 찬 트랜잭션 로그 문제 해결(SQL Server 오류 9002)을 참조하세요.

자동화된 백업 및 트랜잭션 로그

Azure SQL Database에서 트랜잭션 로그 백업은 자동으로 수행됩니다. 빈도, 보존 및 자세한 내용은 자동화된 백업을 참조하세요.

사용 가능한 디스크 공간, 데이터베이스 파일 증가 및 파일 위치도 관리되므로 트랜잭션 로그 문제의 일반적인 원인과 해결 방법이 SQL Server와 다릅니다.

SQL Server와 마찬가지로 각 데이터베이스의 트랜잭션 로그는 로그 백업이 성공적으로 완료될 때마다 잘립니다. 잘린 만큼 로그 파일에 빈 공간이 생기고, 새 트랜잭션에 사용할 수 있게 됩니다. 로그 백업 시 로그 파일을 자를 수 없는 경우 새 트랜잭션을 수용할 수 있도록 로그 파일이 커집니다. 로그 파일이 Azure SQL Database의 최대 한도까지 커지면 새 쓰기 트랜잭션이 실패합니다.

트랜잭션 로그 크기에 대한 자세한 내용은 다음을 참조하세요.

단일 데이터베이스의 vCore 리소스 한도에 대해서는 vCore 구매 모델을 사용하는 단일 데이터베이스의 리소스 한도를 참조하세요.
탄력적 풀의 vCore 리소스 한도에 대해서는 vCore 구매 모델을 사용하는 탄력적 풀의 리소스 한도를 참조하세요.
단일 데이터베이스의 DTU 리소스 한도는 DTU 구매 모델을 사용한 단일 데이터베이스의 리소스 한도를 참조하세요.
탄력적 풀의 DTU 리소스 한도에 대해서는 DTU 구매 모델을 사용하는 탄력적 풀의 리소스 한도를 참조하세요.

트랜잭션 로그 자르기가 차단됨

로그 자르기를 막는 원인을 알아보려면 sys.databases의 log_reuse_wait_desc를 참조하세요. 로그 재사용 대기는 정기적 로그 백업 시에 트랜잭션 로그를 자르지 못하게 막는 조건 또는 원인을 알려줍니다. 자세한 내용은 sys.databases(Transact-SQL)를 참조하세요.

SELECT [name], log_reuse_wait_desc FROM sys.databases;

Azure SQL Database의 경우 master 데이터베이스가 아닌 특정 사용자 데이터베이스에 연결하여 이 쿼리를 실행하는 것이 좋습니다.

sys.databases의 다음 log_reuse_wait_desc 값은 데이터베이스의 트랜잭션 로그 자르기가 차단되는 이유를 나타낼 수 있습니다.

log_reuse_wait_desc	진단	응답 필요
NOTHING	일반적인 상태입니다. 아무 것도 로그 자르기를 차단하지 않습니다.	아니요.
CHECKPOINT	로그 자르기의 검사점이 필요합니다. 매우 드물게 발생합니다.	문제가 지속되지 않는 한 응답이 필요하지 않습니다. 문제가 지속되는 경우 Azure 지원을 통해 지원 요청을 제출합니다.
LOG BACKUP	로그 백업이 필요합니다.	문제가 지속되지 않는 한 응답이 필요하지 않습니다. 문제가 지속되는 경우 Azure 지원을 통해 지원 요청을 제출합니다.
ACTIVE BACKUP OR RESTORE	데이터베이스 백업이 진행 중입니다.	문제가 지속되지 않는 한 응답이 필요하지 않습니다. 문제가 지속되는 경우 Azure 지원을 통해 지원 요청을 제출합니다.
ACTIVE TRANSACTION	진행 중인 트랜잭션이 로그 자르기를 차단하고 있습니다.	활성 및/또는 커밋되지 않은 트랜잭션으로 인해 로그 파일을 자를 수 없습니다. 다음 섹션을 참조하십시오.
REPLICATION	Azure SQL Database에서 CDC(변경 데이터 캡처)를 사용하는 경우 이 문제가 발생할 수 있습니다.	sys.dm_cdc_errors를 쿼리하여 오류를 해결합니다. 해결할 수 없는 경우 Azure 지원을 통해 지원 요청을 제출합니다.
AVAILABILITY_REPLICA	보조 복제본에 대한 동기화가 진행 중입니다.	문제가 지속되지 않는 한 응답이 필요하지 않습니다. 문제가 지속되는 경우 Azure 지원을 통해 지원 요청을 제출합니다.

활성 트랜잭션에 의해 로그 자르기가 차단됨

새 트랜잭션을 수락할 수 없는 트랜잭션 로그에 대한 가장 일반적인 시나리오는 장기 실행 또는 차단된 트랜잭션입니다.

다음과 같은 작업을 수행하는 아래의 샘플 쿼리를 실행하여 커밋되지 않은 트랜잭션 또는 활성 트랜잭션 및 해당 속성을 찾습니다.

sys.dm_tran_active_transactions에서 트랜잭션 속성에 대한 정보를 반환합니다.
sys.dm_exec_sessions에서 세션 연결 정보를 반환합니다.
sys.dm_exec_requests에서 요청 정보(활성 요청의 경우)를 반환합니다. 이 쿼리를 사용하여 차단되는 세션을 식별하고 request_blocked_by를 찾을 수도 있습니다. 자세한 내용은 차단 정보 수집을 참조하세요.
sys.dm_exec_sql_text 또는 sys.dm_exec_input_buffer DMV를 사용하여 현재 요청의 텍스트 또는 입력 버퍼 텍스트를 반환합니다. sys.dm_exec_sql_text의 text 필드에서 반환한 데이터가 NULL이면 요청이 활성 상태는 아니지만 미해결 트랜잭션이 있습니다. 이 경우 sys.dm_exec_input_buffer의 event_info 필드에는 데이터베이스 엔진에 전달된 마지막 명령문이 포함됩니다.

SELECT [database_name] = db_name(s.database_id)
, tat.transaction_id, tat.transaction_begin_time, tst.session_id 
, session_open_transaction_count = tst.open_transaction_count
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime())
, input_buffer = ib.event_info
, request_text = CASE  WHEN r.statement_start_offset = 0 and r.statement_end_offset= 0 THEN left(est.text, 4000)
                       ELSE    SUBSTRING ( est.[text],    r.statement_start_offset/2 + 1, 
                                           CASE WHEN r.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), est.[text])) 
                                                ELSE r.statement_end_offset/2 - r.statement_start_offset/2 + 1
                                           END  )  END
, request_status = r.status
, request_blocked_by = r.blocking_session_id
, transaction_state = CASE tat.transaction_state    
                     WHEN 0 THEN 'The transaction has not been completely initialized yet.'
                     WHEN 1 THEN 'The transaction has been initialized but has not started.'
                     WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
                     WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
                     WHEN 4 THEN 'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.'
                     WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
                     WHEN 6 THEN 'The transaction has been committed.'
                     WHEN 7 THEN 'The transaction is being rolled back.'
                     WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name
, azure_dtc_state    --Applies to: Azure SQL Database only
             =    CASE tat.dtc_state 
                 WHEN 1 THEN 'ACTIVE'
                 WHEN 2 THEN 'PREPARED'
                 WHEN 3 THEN 'COMMITTED'
                 WHEN 4 THEN 'ABORTED'
                 WHEN 5 THEN 'RECOVERED' END
, transaction_type = CASE tat.transaction_type    WHEN 1 THEN 'Read/write transaction'
                                             WHEN 2 THEN 'Read-only transaction'
                                             WHEN 3 THEN 'System transaction'
                                             WHEN 4 THEN 'Distributed transaction' END
, tst.is_user_transaction
, local_or_distributed = CASE tst.is_local WHEN 1 THEN 'Local transaction, not distributed' WHEN 0 THEN 'Distributed transaction or an enlisted bound session transaction.' END
, transaction_uow    --for distributed transactions. 
, s.login_time, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
, session_cpu_time = s.cpu_time, session_logical_reads = s.logical_reads, session_reads = s.reads, session_writes = s.writes
, observed = sysdatetimeoffset()
FROM sys.dm_tran_active_transactions AS tat 
INNER JOIN sys.dm_tran_session_transactions AS tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions AS s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests AS r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib 
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) AS est;

더 많은 공간을 확보하도록 파일 관리

트랜잭션 로그가 Azure SQL Database 탄력적 풀에서 잘리지 않는 경우 탄력적 풀에 대한 공간 확보가 솔루션에 포함될 수 있습니다. 그러나 트랜잭션 로그 파일 자르기를 차단하는 근본 원인을 해결하는 것이 중요합니다. 경우에 따라 임시로 더 많은 디스크 공간을 생성하면 장기 실행 트랜잭션이 완료되고, 일반적인 트랜잭션 로그 백업 시 트랜잭션 로그 파일 자르기를 차단하는 조건이 제거됩니다. 하지만 공간을 확보하더라도 트랜잭션 로그가 다시 증가할 때까지 일시적으로만 문제가 완화됩니다.

데이터베이스 및 탄력적 풀의 파일 공간 관리에 대한 자세한 내용은 Azure SQL Database의 데이터베이스에 대한 파일 공간 관리를 참조하세요.

오류 40552: 트랜잭션 로그 공간 사용량이 너무 많아 세션이 종료됨

40552: The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.

이 문제를 해결하려면 다음 방법을 사용해 보세요.

이 문제는 삽입, 업데이트 또는 삭제와 같은 모든 DML 작업에서 발생할 수 있습니다. 트랜잭션을 검토하여 불필요한 쓰기를 방지하세요. 일괄 처리를 구현하거나 여러 개의 여러 개의 작은 트랜잭션으로 분할하여 즉시 실행되는 행 수를 줄여 보세요. 자세한 내용은 배치를 사용하여 SQL Database 애플리케이션 성능을 개선하는 방법을 참조하세요.
인덱스 다시 빌드 작업으로 인해 이 문제가 발생할 수 있습니다. 이 문제를 방지하려면 (테이블에서 영향을 받는 행의 수) * (업데이트된 필드의 평균 크기(바이트) + 80) < 2GB(기가바이트) 수식이 true이어야 합니다. 대형 테이블의 경우 파티션을 만들고 테이블의 일부 파티션에서만 인덱스 유지 관리를 수행하는 것이 좋습니다. 자세한 내용은 분할된 테이블 및 인덱스 만들기를 참조하세요.
bcp.exe 유틸리티 또는 System.Data.SqlClient.SqlBulkCopy 클래스를 사용하여 대량 삽입을 수행하는 경우 -b batchsize 또는 BatchSize 옵션을 사용하여 각 트랜잭션에서 서버로 복사된 행의 수를 제한하세요. 자세한 내용은 bcp Utility를 참조하세요.
ALTER INDEX 문을 사용하여 인덱스를 다시 작성하는 경우 SORT_IN_TEMPDB = ON, ONLINE = ON 및 RESUMABLE=ON 옵션을 사용합니다. 다시 시작하는 인덱스를 사용하면 로그 잘림이 더 자주 발생합니다. 자세한 내용은 ALTER INDEX(Transact-SQL)를 참조하세요.

참고

기타 리소스 관리자 오류에 대한 자세한 내용은 리소스 거버넌스 오류를 참조하세요.