Monday, 6 January 2014

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)