SELECT tst.session_id, [database_name] =
db_name(s.database_id)
, tat.transaction_begin_time
, transaction_duration_s = datediff(s,
tat.transaction_begin_time, sysdatetime())
, transaction_type = CASE
tat.transaction_type WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN
'Read-only transaction'
WHEN 3 THEN
'System transaction'
WHEN 4 THEN
'Distributed transaction' END
, input_buffer = ib.event_info,
tat.transaction_uow
, transaction_state = CASE
tat.transaction_state
WHEN 0 THEN
'The transaction has not been completely initialized yet.'
WHEN 1 THEN 'The
transaction has been initialized but has not started.'
WHEN 2 THEN 'The
transaction is active - has not been committed or rolled back.'
WHEN 3 THEN 'The
transaction has ended. This is used for read-only transactions.'
WHEN 4 THEN 'The
commit process has been initiated on the distributed transaction.'
WHEN 5 THEN 'The
transaction is in a prepared state and waiting resolution.'
WHEN 6 THEN 'The
transaction has been committed.'
WHEN 7 THEN 'The
transaction is being rolled back.'
WHEN 8 THEN 'The
transaction has been rolled back.' END
, transaction_name = tat.name,
request_status = r.status
, tst.is_user_transaction, tst.is_local
, session_open_transaction_count =
tst.open_transaction_count
, s.host_name, s.program_name,
s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat
INNER JOIN sys.dm_tran_session_transactions tst on
tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions s on s.session_id =
tst.session_id
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id =
s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS
ib
ORDER BY tat.transaction_begin_time DESC;
