Skip to content

Instantly share code, notes, and snippets.

@stummsft
Last active March 5, 2019 16:16
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save stummsft/1260cdc7f52609ae615c36ae1f4fc824 to your computer and use it in GitHub Desktop.
Save stummsft/1260cdc7f52609ae615c36ae1f4fc824 to your computer and use it in GitHub Desktop.
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