/*
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())