Azure SQL Managed Instance의 트랜잭션 로그 오류 문제 해결

아티클
03/19/2024

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

참고 항목

이 문서에서는 Azure SQL Managed Instance를 중점적으로 다룹니다. Azure SQL Managed Instance는 안정적인 최신 버전의 Microsoft SQL Server 데이터베이스 엔진을 기반으로 하므로 문제 해결 옵션과 도구는 SQL Server와 다를 수 있지만 많은 내용이 유사합니다.

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

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

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

Azure SQL Managed Instance에서는 트랜잭션 로그 백업이 자동으로 수행됩니다. 빈도, 보존 기간 및 자세한 내용은 자동화된 백업을 참조하세요. SQL Managed Instance에서 자동화된 백업이 수행된 시기를 추적하려면 백업 작업 모니터링을 참조하세요.

데이터베이스 파일의 위치와 이름을 관리할 수 없지만, 관리자는 데이터베이스 파일 및 파일 자동 증가 설정을 관리할 수 있습니다. 트랜잭션 로그 문제의 일반적인 원인과 해결 방법은 SQL Server와 비슷합니다.

SQL Server와 마찬가지로 각 데이터베이스의 트랜잭션 로그는 로그 백업이 성공적으로 완료될 때마다 잘립니다. 로그 자르기는 트랜잭션 로그에서 비활성 VLF(가상 로그 파일)를 삭제하여 파일 내부의 공간을 확보하지만 디스크의 파일 크기는 변경하지 않습니다. 로그 파일에 빈 공간이 생기면 새 트랜잭션에 사용할 수 있습니다. 로그 백업 시 로그 파일을 자를 수 없는 경우 새 트랜잭션을 수용할 수 있도록 로그 파일이 커집니다. 로그 파일이 Azure SQL Managed Instance의 최대 한도까지 커지면 새 쓰기 트랜잭션이 실패합니다.

Azure SQL Managed Instance에서 컴퓨팅과 별도로 추가 기능 스토리지를 한도 내에서 구매할 수 있습니다. 자세한 내용은 더 많은 공간을 확보하기 위한 파일 관리를 참조하세요.

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

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

SELECT [name], log_reuse_wait_desc FROM sys.databases;

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 Managed Instance에서 복제 또는 CDC를 사용하는 경우 발생할 수 있습니다.	문제가 지속되는 경우 CDC 또는 복제와 관련된 에이전트를 조사합니다. CDC 문제 해결을 위해 msdb.dbo.cdc_jobs의 작업을 쿼리합니다. 작업이 없는 경우 sys.sp_cdc_add_job을 통해 추가합니다. 복제인 경우 트랜잭션 복제 문제 해결을 참조하세요. 해결할 수 없는 경우 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
, 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 Managed Instance에서 트랜잭션 로그가 잘리지 않는 경우 공간을 확보하는 것이 솔루션의 일부일 수 있습니다. 그러나 트랜잭션 로그 파일 자르기를 차단하는 근본 원인을 해결하는 것이 중요합니다. 경우에 따라 임시로 더 많은 디스크 공간을 생성하면 장기 실행 트랜잭션이 완료되고, 일반적인 트랜잭션 로그 백업 시 트랜잭션 로그 파일 자르기를 차단하는 조건이 제거됩니다. 하지만 공간을 확보하더라도 트랜잭션 로그가 다시 증가할 때까지 일시적으로만 문제가 완화됩니다.

Azure SQL Managed Instance에서 컴퓨팅과 별도로 추가 기능 스토리지를 한도 내에서 구매할 수 있습니다. 예를 들어 Azure Portal에서 컴퓨팅 + 스토리지 페이지에 액세스하여 스토리지(GB)를 늘립니다. 트랜잭션 로그 크기 제한에 대한 자세한 내용은 SQL Managed Instance에 대한 리소스 한도를 참조하세요. 자세한 내용은 Azure SQL Managed Instance에서 데이터베이스의 파일 공간 관리를 참조하세요.

백업 스토리지는 SQL Managed Instance 스토리지 공간에서 공제되지 않습니다. 백업 스토리지는 인스턴스 스토리지 공간과 독립적으로 운영되며 크기 제한이 없습니다.

오류 9002: 데이터베이스의 트랜잭션 로그가 꽉 찼습니다.

9002: The transaction log for database '%.*ls' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

같은 이유로 SQL Server와 Azure SQL Managed Instance에서 오류 9002가 발생합니다.

가득 찬 트랜잭션 로그에 대한 적절한 응답은 로그가 꽉 차게 만든 조건에 따라 달라집니다.

오류 9002를 해결하려면 다음 방법을 사용해 보세요.

트랜잭션 로그가 잘리지 않고 사용 가능한 공간을 모두 채울 만큼 커졌습니다.
- Azure SQL Managed Instance의 트랜잭션 로그 백업은 자동으로 수행되므로 트랜잭션 로그 작업이 잘리지 않도록 해야 합니다. 불완전한 복제, CDC 또는 가용성 그룹 동기화로 인해 잘림이 방지될 수 있습니다. 트랜잭션 로그 잘림 방지를 참조하세요.
SQL Managed Instance 예약 스토리지 크기가 가득 차서 트랜잭션 로그를 늘릴 수 없습니다.
- 리소스 제한까지 공간을 추가합니다. 더 많은 공간을 확보하기 위한 파일 관리를 참조하세요.
트랜잭션 로그 크기가 고정된 최대값으로 설정되어 있거나 자동 증가가 사용하지 않도록 설정되어 증가하지 않습니다.
- ALTER DATABASE 파일 및 파일 그룹의 MAXSIZE 및 FILEGROWTH 속성을 참조하세요.

오류 40552: 트랜잭션 로그 공간 사용량이 너무 많아 세션이 종료되었습니다.

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

오류 9002는 Azure SQL Managed Instance의 오류 40552보다 일반적이지만 둘 다 발생할 수 있습니다.