How to find Open SQL Transactions

 During a blocking and deadlock scenario, identifying Open Transactions is part of the troubleshooting process. Once the Open Transactions are identified the DBA can proceed with analysing the transactions for improved efficiencies.

 There are two  main methods of finding SQL Server Open Transaction

Method 1 – DBCC OPENTRAN()

Read SQL Server - SQL open transactions and how to find  for details on using DBCC OPENTRAN()   and DBCC INPUTBUFFER() for details

Method 2 – DMV : sys.dm_tran_database_transactions and sys.dm_tran_session_transactions

If you prefer , use the DMV the sys.dm_tran_database_transactions and sys.dm_tran_session_transactions  , return similar information , but I find more effective in processing the information

SELECT dt.transaction_id,
       st.session_id,
       database_transaction_begin_time,
       CASE database_transaction_type
         WHEN 1 THEN 'Read/write transaction'
         WHEN 2 THEN 'Read-only transaction'
         WHEN 3 THEN 'System transaction'
       END database_transaction_type,
       CASE database_transaction_state
         WHEN 1 THEN 'The transaction has not been initialized.'
         WHEN 3 THEN 'The transaction has been initialized but has not generated any log recorst.'
         WHEN 4 THEN 'The transaction has generated log recorst.'
         WHEN 5 THEN 'The transaction has been prepared.'
         WHEN 10 THEN 'The transaction has been committed.'
         WHEN 11 THEN 'The transaction has been rolled back.'
         WHEN 12 THEN 'The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted'
       END database_transaction_state,
       database_transaction_log_bytes_used,
       database_transaction_log_bytes_reserved
       
FROM   sys.dm_tran_database_transactions dt
       INNER JOIN sys.dm_tran_session_transactions st
           ON st.transaction_id = dt.transaction_id

 Read More

 SQL Server - Cached blocking history with sys.dm_db_index_operational_stats

Blocked processes - SQL Server DBA

SQL Server – LCK_M_X and how to reduce it