Monitoring Script to Identify OPEN TRANSACTION – SQL SERVER

In a Production environment, monitoring open transaction list is recommended to avoid blocking issues. As a best practice, if we use transactions inside the programming scripts, it should also have scripts to COMMIT\ROLLBACK those transactions.

SQL Server has many DMVs to identify the OPEN Transactions.

Below scripts will be useful to identify OPEN Transactions in details as well as by HOST Name.

-- Script to List Number of OPEN TRANS by Host Name

SELECT

                        Host_name,

                        SUM(DTST.Enlist_count) [Nos of OPEN TRANS]               

FROM

                        SYS.Dm_tran_session_transactions DTST,

                        SYS.Dm_exec_sessions DES

WHERE

                        DTST.Session_id                =         DES.Session_id

GROUP BY

                        Host_name

 -- Script to list OPEN TRANS in detail

SELECT

                        DTST.Session_id,

                        DTST.Enlist_count,

                        Host_name,

                        Program_name,

                        Text

FROM

                       SYS.Dm_tran_session_transactions DTST

                        INNER JOIN SYS.Dm_exec_connections DEC ON DTST.Session_id            = DEC.Session_id

                        INNER JOIN SYS.Dm_exec_sessions DES ON DTST.Session_id               = DES.Session_id

                        CROSS APPLY SYS.DM_EXEC_SQL_TEXT(DEC.Most_recent_sql_handle)