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