Il log delle transazioni aumenta a causa di transazioni a esecuzione prolungata quando si usa Change Data Capture - SQL Server e SQL di Azure
Questo articolo illustra come monitorare e identificare il problema di aumento continuo del log delle transazioni causato da transazioni a esecuzione prolungata in un database con Change Data Capture (CDC) abilitato in SQL Server, database SQL di Azure e Istanza gestita di SQL di Azure.
Sintomi
Prendi in considerazione lo scenario seguente:
- È possibile abilitare Change Data Capture in un database.
- L'origine dei dati delle modifiche per CDC è il log delle transazioni. Man mano che alle tabelle di origine con rilevamento vengono applicati inserimenti, aggiornamenti ed eliminazioni, le voci che descrivono tali modifiche vengono aggiunte al log.
- Il log delle transazioni nel database aumenta a causa di transazioni a esecuzione prolungata.
- Quando si esegue una query su sys.databases per il database specificato, la
log_reuse_wait_desccolonna mostraREPLICATION.
In questo scenario, il file di log delle transazioni del database aumenta gradualmente, causando un consumo eccessivo dello spazio del log delle transazioni. Quando le dimensioni del log delle transazioni raggiungono il limite massimo definito, le scritture nel database hanno esito negativo.
Causa
In un database abilitato per CDC la latenza del processo di acquisizione mantiene il troncamento del log per garantire che le modifiche possano essere acquisite dal log delle transazioni alle tabelle delle modifiche CDC, impedendo la perdita di dati delle modifiche.
Soluzione alternativa
È possibile usare Transact-SQL (T-SQL) per specificare la soglia del log delle transazioni e l'intervallo di tempo per monitorare il log delle transazioni. Se necessario, è possibile terminare le transazioni impostando @kill_oldest_tran = 1.
Per monitorare il log delle transazioni, usare la query T-SQL seguente:
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