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