Experts Exchange connects you with the people and services you need so you can get back to work.
SELECT ses.login_time AS session_login_time, ses.last_request_start_time, ses.last_request_end_time, ses.login_name, ses.nt_user_name, ses.status,
tst.session_id, tst.enlist_count AS nr_active_reqs_in_session, CASE tst.is_user_transaction
WHEN 1 THEN 'Transaction initiated by user request'
WHEN 0 THEN 'System transaction'
END AS tran_status,
WHEN 1 THEN 'Local transaction'
WHEN 0 THEN 'Distributed transaction'
END AS is_local,
WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
WHEN 4 THEN 'Distributed transaction'
END AS tran_type,
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'
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 AS transaction_state,
WHEN 1 THEN 'ACTIVE'
WHEN 2 THEN 'PREPARED'
WHEN 3 THEN 'COMMITTED'
WHEN 4 THEN 'ABORTED'
WHEN 5 THEN 'RECOVERED'
END AS dtc_state,
WHEN 1 THEN 'active via bound sessions'
WHEN 0 THEN 'not active via bound sessions'
END AS bound_status
FROM sys.dm_exec_sessions AS ses JOIN sys.dm_tran_session_transactions AS tst
JOIN sys.dm_tran_active_transactions AS tat
ON tst.transaction_id = tat.transaction_id
Open in new window
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment
Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.