SQL Server - How to find Open Transactions

12 November,2012 by Jack Vamvas

 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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

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

Working...
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.

Working...

Post a comment on SQL Server - How to find Open Transactions


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer