067. Find Open transactions in SQL Server

-- 067. Find Open transactions in SQL Server
 
SELECT 
     tdt.transaction_id
     ,tst.session_id
     ,tdt.database_transaction_begin_time
     ,CASE tdt.database_transaction_type
        WHEN 1 THEN 'Read/write transaction'
	WHEN 2 THEN 'Read only transaction'
	WHEN 3 THEN 'System transaction'
      END transaction_type
     ,CASE tdt.database_transaction_state
	WHEN 1 THEN 'Transaction not initialized'
	WHEN 3 THEN 'Transaction has not generated by any log'
	WHEN 4 THEN 'Transaction has generated by log'
	WHEN 5 THEN 'Transaction Prepared'
	WHEN 10 THEN 'Transaction Committed'
	WHEN 11 THEN 'Transaction Rolled back'
	WHEN 12 THEN 'Transaction committed and log generated'
     END transaction_state     
FROM sys.dm_tran_database_transactions tdt
INNER JOIN sys.dm_tran_session_transactions tst
  ON tst.transaction_id = tdt.transaction_id