Tag Archives: opentran

Query to get Open Transaction

Posted on by

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