Query to get Open Transaction

Query to get Open Transaction.

Query to monitor open transactions in your sql server instance.

If you are using SQL Server 2012 use open_transaction_count column instead of enlist_count.

Query to get open transactions by host. remove the comment if you want open transactions just from ssms window.

SELECT
SUM(Tst.Enlist_count) Noofopentran,
Host_name
FROM   SYS.Dm_tran_session_transactions Tst
INNER JOIN SYS.Dm_exec_sessions Es
ON Tst.Session_id = Es.Session_id
--WHERE program_name = 'Microsoft SQL Server Management Studio - Query'
GROUP  BY Host_name

List Open transactions in details.


SELECT
Tst.Session_id,
Tst.Enlist_count,
Host_name,
Program_name,
Text
FROM   SYS.Dm_tran_session_transactions Tst
INNER JOIN SYS.Dm_exec_connections Ec
ON Tst.Session_id = Ec.Session_id
INNER JOIN SYS.Dm_exec_sessions Es
ON Tst.Session_id = Es.Session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(Ec.Most_recent_sql_handle)
--where program_name = 'Microsoft SQL Server Management Studio - Query'

Regards,
Nirav Gajjar