Active transactions

active_transactions.sql

select	datediff(minute, dtdt.database_transaction_begin_time, getutcdate()) as TransactionDurationMinutes
		,d.name as DatabaseName
		,dtst.session_id
		,dtdt.database_transaction_log_record_count
		,cast(dtdt.database_transaction_log_bytes_used / 1024. / 1024. as decimal(17,2)) as LogUsedMB
		,des.program_name
		,des.host_name
		,des.nt_user_name
		,map.*
		,dest.*
		,dtdt.*
from sys.dm_tran_database_transactions as dtdt
left join sys.dm_tran_session_transactions as dtst
on dtst.transaction_id = dtdt.transaction_id
left join sys.dm_exec_sessions as des
on des.session_id = dtst.session_id
left join sys.dm_exec_connections as dec
on dec.session_id = dtst.session_id 
left join sys.dm_exec_requests as der
on der.session_id = dtst.session_id
outer apply sys.dm_exec_sql_text(der.sql_handle) as dest
join sys.databases as d
on d.database_id = dtdt.database_id
cross apply (
	select case dtdt.database_transaction_type
				when 1 then 'Read/Write'
				when 2 then 'Read Only'
				when 3 then 'System'
				else 'Other'
			end as TransactionType
			,case dtdt.database_transaction_state
				when 1 then 'Not initialized'
				when 3 then 'Initialized but no log records'
				when 4 then 'Generated log records'
				when 5 then 'Prepared'
				when 10 then 'Committed'
				when 11 then 'Rolled back'
				when 12 then 'Being committed'
				else 'Other'
			end as TransactionState
) as map
where dtdt.database_transaction_begin_time is not null
order by dtdt.database_transaction_begin_time asc