Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

How to find Open SQL Transactions

12 November,2012 by Tom Collins

 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,
       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,
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

Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on How to find Open SQL Transactions | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer