Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Gather info about long running snapshot transactions
/*
This query grabs relevant information about active long-running snapshot transactions.
This can help identify what is preventing cleanup of tempdb version store records.
This can also help identify what is preventing long truncation in an Availability Group topology.
*/
DECLARE @old_transaction_cutoff_minutes INT = 60;
SELECT
SYSDATETIME() AS [runtime]
, [s].[last_request_start_time] AS [last_batch]
, COALESCE([tst].[open_transaction_count], [r].[open_transaction_count]) AS [open_transaction_count]
, [tat].[transaction_id]
, [tasdt].[transaction_sequence_num]
, [tasdt].[commit_sequence_num]
, [s].[session_id] AS [session_id]
, [tasdt].[is_snapshot]
, [tasdt].[first_snapshot_sequence_num]
, [tasdt].[max_version_chain_traversed]
, [tasdt].[average_version_chain_traversed]
, [tasdt].[elapsed_time_seconds]
, NULL AS [kpid]
, CASE [r].[blocking_session_id]
WHEN 0 THEN NULL
WHEN NULL THEN NULL
ELSE [r].[blocking_session_id]
END AS [blocking_session_id]
, [r].[wait_time]
, [r].[last_wait_type]
, [s].[database_id] AS [session_database_id]
, [tdt].[database_id] AS [transaction_database_id]
, [s].[cpu_time] + COALESCE([r].[cpu_time], 0) AS [cpu_time]
, [s].[reads] + COALESCE([r].[reads], 0) AS [reads]
, [s].[writes] + COALESCE([r].[writes], 0) AS [writes]
, [s].[logical_reads] + COALESCE([r].[logical_reads], 0) AS [logical_reads]
, [s].[memory_usage] + COALESCE([r].[granted_query_memory], 0) AS [memory_usage]
, [c].[connect_time]
, [s].[status]
, [c].[client_net_address]
, [s].[program_name]
, [r].[command]
, [s].[login_name]
, [r].[request_id]
, COALESCE([tdt].[total_reserved_bytes], 0) AS [log_reserved_bytes]
, [tat].[name] AS [transaction_name]
, CASE [tst].[open_transaction_count]
WHEN NULL THEN 0
ELSE 1
END AS [is_explicit_transaction]
FROM [sys].[dm_tran_active_transactions] [tat]
INNER JOIN (
SELECT
[tdt].[transaction_id]
, [tdt].[database_id]
, SUM([tdt].[database_transaction_log_bytes_reserved] + [tdt].[database_transaction_log_bytes_reserved_system]) AS [total_reserved_bytes]
FROM [sys].[dm_tran_database_transactions] [tdt]
WHERE [tdt].[database_id] NOT IN (1, 2, 3, 4) --Ignore the built-in databases
GROUP BY [tdt].[transaction_id], [tdt].[database_id]
) [tdt]
ON [tdt].[transaction_id] = [tat].[transaction_id]
LEFT JOIN [sys].[dm_tran_session_transactions] [tst] --Empty for implicit transactions
ON [tst].[transaction_id] = [tat].[transaction_id]
LEFT JOIN [sys].[dm_tran_active_snapshot_database_transactions] [tasdt]
ON [tasdt].[transaction_id] = [tat].[transaction_id]
INNER JOIN [sys].[dm_exec_sessions] [s]
ON [s].[session_id] = COALESCE([tst].[session_id], [tasdt].[session_id])
INNER JOIN [sys].[dm_exec_connections] [c]
ON [c].[session_id] = [s].[session_id]
LEFT JOIN [sys].[dm_exec_requests] [r] --Empty for idle sessions
ON [r].[transaction_id] = [tat].[transaction_id]
WHERE
[tat].[transaction_begin_time] < DATEADD(MINUTE, 0 - @old_transaction_cutoff_minutes, SYSDATETIME())
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment