sqlt-sqltransactionsdmvworkload

How to List Open Transactions

I'm trying to find a way to list all open transactions in SQL server.

For example I execute following statement:

enter image description here

This opens two transactions which complete, but now this session get set to "sleeping" with two opened transactions. How I can see these open transaction statements?

When I run

select * from sys.dm_tran_session_transactions

enter image description here

I can see there is only one row with the transaction ID and opened tran count. (session 57 is mine with open transactions)

When I run

select * from sys.dm_tran_database_transactions

enter image description here

I see the two rows for my transaction_id; one in user_db and one in tempdb (why do I only see two rows? I have two open transactions in user_db so I would expect to see more rows with db id 5

When I run

select * from sys.dm_tran_active_transactions

enter image description here

Nothing helpful , only one row with no details.

The ability to view open transaction statements on sleeping sessions may sound trivial but I cannot find any way to see these.

Solution

  • It was done by predefined EE session with specific filter by session ID , from this I could compare collected data with current open transaction by transactiond_id. It looks like this unbelievable to check transaction text without predefined logging.