변경 데이터 캡처를 사용할 때 장기 실행 트랜잭션으로 인해 트랜잭션 로그가 증가합니다. - SQL Server 및 Azure SQL
이 문서는 SQL Server, Azure SQL Database 및 Azure SQL Managed Instance에서 CDC(변경 데이터 캡처)를 사용하도록 설정된 데이터베이스에서 장기 실행 트랜잭션으로 인한 지속적인 트랜잭션 로그 증가 문제를 모니터링하고 식별하는 데 도움이 됩니다.
증상
다음 시나리오를 고려하세요.
- 데이터베이스에서 변경 데이터 캡처를 사용하도록 설정합니다.
- CDC에 대한 변경 데이터의 원본은 트랜잭션 로그입니다. 추적된 원본 테이블에 삽입, 업데이트 및 삭제가 적용되면 이러한 변경을 설명하는 항목이 로그에 추가됩니다.
- 장기 실행 트랜잭션으로 인해 데이터베이스의 트랜잭션 로그가 증가합니다.
- 지정된 데이터베이스에 대해 sys.databases를 쿼리하면 열이
log_reuse_wait_desc표시됩니다REPLICATION.
이 시나리오에서는 데이터베이스 트랜잭션 로그 파일이 점차 증가하여 트랜잭션 로그 공간 사용량이 너무 많아집니다. 트랜잭션 로그 크기가 정의된 최대 한도에 도달하면 데이터베이스에 대한 쓰기가 실패합니다.
원인
CDC 지원 데이터베이스에서 캡처 작업 대기 시간은 로그 잘림을 유지하여 트랜잭션 로그에서 CDC 변경 테이블로 변경 내용을 캡처하여 변경 데이터 손실을 방지합니다.
해결 방법
Transact-SQL(T-SQL)을 사용하여 트랜잭션 로그 임계값 및 시간 간격을 지정하여 트랜잭션 로그를 모니터링할 수 있습니다. 필요한 경우 를 설정 @kill_oldest_tran = 1하여 트랜잭션을 종료할 수 있습니다.
트랜잭션 로그를 모니터링하려면 다음 T-SQL 쿼리를 사용합니다.
DECLARE
-- Log Transactions that generated Txlog over this size
@transaction_log_bytes_used INT = 5242880, -- 5MB (UPDATE)
-- Log full threshold
@log_full_threshold INT = 30, -- Percent (UPDATE)
-- Kill Oldest Tran (0 = FALSE or 1 = TRUE)
@kill_oldest_tran BIT = 0, --(UPDATE)
-- Log Transactions over this duration
@active_tran_time_minutes INT = 15, --(UPDATE)
-- This variable specifies the loop delay, format is Hours:minutes:seconds
@delay VARCHAR(9) = '00:10:00',
@runtime DATETIME,
@starttime DATETIME,
@msg NVARCHAR(100),
@oldest_tran_id BIGINT,
@oldest_tran_session_id INT,
@oldest_tran_begin_time DATETIME,
@killstr NVARCHAR(100)
IF OBJECT_ID('tblDiagLongTransactions') IS NULL
BEGIN
CREATE TABLE tblDiagLongTransactions
(
[datacollectiontime] [datetime] NOT NULL,
[transaction_id] [bigint] NOT NULL,
[name] [nvarchar](32) NOT NULL,
[transaction_begin_time] [datetime] NOT NULL,
[transaction_type] [int] NOT NULL,
[transaction_state] [int] NOT NULL,
[session_id] [int] NOT NULL,
[is_user_transaction] [bit] NOT NULL,
[database_transaction_log_bytes_used] [bigint] NOT NULL,
[login_time] [datetime] NOT NULL,
[last_request_start_time] [datetime] NOT NULL,
[last_request_end_time] [datetime] NULL,
[transaction_isolation_level] [smallint] NOT NULL,
[host_name] [nvarchar](128) NULL,
[nt_user_name] [nvarchar](128) NULL,
[command] [nvarchar](32) NULL,
[status] [nvarchar](30) NULL,
[cpu_time] [int] NULL,
[total_elapsed_time] [int] NULL,
[Transaction_time_in_mins] [int] NULL,
[logical_reads] [bigint] NULL,
[wait_time] [int] NULL,
[wait_type] [nvarchar](60) NULL,
[wait_resource] [nvarchar](256) NULL,
[blocking_session_id] [smallint] NULL,
[program_name] [nvarchar](128) NULL,
[granted_query_memory] [int] NULL,
[writes] [bigint] NULL,
[Request Reads] [bigint] NULL,
[Session Reads] [bigint] NOT NULL,
[Session Logical Reads] [bigint] NOT NULL,
[statement_text] [nvarchar](max) NULL,
[batch_text] [nvarchar](max) NULL,
[objectid] [int] NULL,
[query_hash] BINARY(8),
[query_plan_hash] BINARY(8),
[mostrecentsqltext] [nvarchar](max) NULL
) ON [PRIMARY]
END
WHILE (1=1)
BEGIN
-- Check if the database log used space is over the threshold
SET @runtime = GETDATE()
INSERT INTO tblDiagLongTransactions
SELECT DISTINCT
@runtime AS datacollectiontime,
atr.transaction_id,
atr.name,
transaction_begin_time,
transaction_type,
transaction_state,
dsr.session_id,
dsr.is_user_transaction,
dtr.database_transaction_log_bytes_used,
s.login_time,
s.last_request_start_time,
s.last_request_end_time,
s.transaction_isolation_level,
s.host_name,
s.nt_user_name,
r.command,
r.status,
r.cpu_time,
r.total_elapsed_time,
DATEDIFF(mi, transaction_begin_time, getdate()) AS 'Transaction_time_in_mins',
r.logical_reads,
r.wait_time,
r.wait_type,
r.wait_resource,
r.blocking_session_id,
s.program_name,
r.granted_query_memory,
r.writes,
r.reads AS [Request Reads],
s.reads AS [Session Reads],
s.logical_reads AS [Session Logical Reads],
(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(qt.text, r.statement_start_offset / 2 + 1,
(CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2), ' ', ''), CHAR(13), ''), CHAR(10), ''), CHAR(9), '')) AS statement_text,
SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(qt.text, ' ', ''), CHAR(13), ''), CHAR(10), ''), CHAR(9), ''), 1, 256) AS batch_text,
qt.objectid,
query_hash,
query_plan_hash,
mqt.text
FROM sys.dm_tran_active_transactions atr
INNER JOIN sys.dm_tran_database_transactions dtr ON atr.transaction_id = dtr.transaction_id
INNER JOIN sys.dm_tran_session_transactions dsr ON atr.transaction_id = dsr.transaction_id
LEFT OUTER JOIN sys.dm_exec_sessions s ON dsr.session_id = s.session_id
LEFT OUTER JOIN sys.dm_exec_connections conn ON s.session_id = dsr.session_id
LEFT OUTER JOIN sys.dm_exec_requests r ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt
OUTER APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle) AS mqt
WHERE s.session_id != @@spid
AND atr.transaction_type != 2
AND (database_transaction_log_bytes_used > @transaction_log_bytes_used
OR datediff(minute, transaction_begin_time, getdate()) > @active_tran_time_minutes)
-- Check Log full threshold
IF @kill_oldest_tran = 1
BEGIN
IF EXISTS (
SELECT 1 FROM sys.dm_db_log_space_usage
WHERE used_log_space_in_percent >= @log_full_threshold
)
BEGIN
SELECT TOP 1
@oldest_tran_id = atr.transaction_id,
@oldest_tran_begin_time = transaction_begin_time,
@oldest_tran_session_id = dsr.session_id
FROM sys.dm_tran_active_transactions atr
LEFT OUTER JOIN sys.dm_tran_database_transactions dtr ON atr.transaction_id = dtr.transaction_id
LEFT OUTER JOIN sys.dm_tran_session_transactions dsr ON atr.transaction_id = dsr.transaction_id
LEFT OUTER JOIN sys.dm_exec_sessions s ON dsr.session_id = s.session_id
WHERE dsr.session_id != @@spid
AND is_user_transaction = 1
ORDER BY transaction_begin_time DESC
SELECT @oldest_tran_id AS TranID, @oldest_tran_begin_time AS TranbeginTime, @oldest_tran_session_id AS SessionID
SET @killstr = 'KILL ' + CAST(@oldest_tran_session_id AS VARCHAR(100))
PRINT @killstr
-- Kill oldest tran
EXEC (@killstr)
-- Checkpoint
CHECKPOINT
END
END
-- Change the polling interval as required
WAITFOR DELAY @delay
END