How to Obtain SQL Active Transaction Information

How can we help you today?

Issue
How to list all active SQL transactions along with their request data like transaction start, effected rows, bytes to log and wait types to analyze current database activities.

Product Line
ION EEM
ION Enterprise 6.0. x
Power Monitoring Expert (PME) 7.2.x
Power Monitoring Expert (PME) 8.x
Power Monitoring Expert 9.0
Power Monitoring Expert 2020
Power Monitoring Expert 2021
Power Monitoring Expert 2022
Power Monitoring Expert 2023
Power Monitoring Expert 2024

Environment
SQL Server 2005, SQL Server 2008 R 2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, SQL Server 2022

Cause
In order to analyze and troubleshoot certain database issues or performance impediments, we may need to view the current database activities as they are occurring.

Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

With this script you can view all currently running transactions along with their associated detailed request data.
This script will allow analysis of:
- all currently running transactions with detailed data
- the current sql statement executing in this transaction
- the progress & activities of long running DML statement and the amount of data written to log
- long active transaction
- which transactions starts when you run system commands

To obtain this list of all active transactions, log into SQL Server Management Studio and run the attached SQL script (Active_Transactions.sql).
Alternatively, execute the query(s) below:

SELECT CASE WHEN TDT.database_id = 32767 
            THEN 'MSSQLSystemResource' 
            ELSE DB.name END AS DatabaseName       
      ,REQ.start_time AS ReqStart 
      ,TAT.transaction_begin_time AS TransBegin 
      ,TAT.name AS TransName 
      ,CASE TDT.database_transaction_type 
           WHEN 1 THEN N'Read/Write' 
           WHEN 2 THEN N'Read-only' 
           WHEN 3 THEN N'System' 
           ELSE N'Unkown' END AS TransType 
      ,CASE TAT.transaction_state 
           WHEN 0 THEN N'Not initialized' 
           WHEN 1 THEN N'Not started' 
           WHEN 2 THEN N'Active' 
           WHEN 3 THEN N'Ended' 
           WHEN 4 THEN N'DTC active' 
           WHEN 5 THEN N'Preparing' 
           WHEN 6 THEN N'Committing' 
           WHEN 7 THEN N'Being rolled back' 
           WHEN 8 THEN N'Rolled back' 
           ELSE N'Unkown' 
       END AS TransState 
      ,REQ.[status] AS ReqStatus 
      ,TDT.database_transaction_log_record_count AS LogRec 
      ,TDT.database_transaction_log_bytes_used AS LogBytes 
      ,SES.login_name AS LoginName      
      ,REQ.wait_type AS ReqWaitType 
      ,REQ.percent_complete AS [ReqCompl%] 
      ,REQ.command AS ReqCommand 
      ,SUBSTRING(EST.text 
                ,1 + REQ.statement_start_offset / 2 
                ,(CASE WHEN REQ.statement_end_offset = -1  
                       THEN LEN(convert(nvarchar(max), EST.text)) * 2  
                       ELSE REQ.statement_end_offset END  
                 - REQ.statement_start_offset) / 2 
                ) AS SqlStatement 
FROM sys.dm_tran_active_transactions AS TAT 
     INNER JOIN sys.dm_tran_database_transactions AS TDT 
         ON TAT.transaction_id = TDT.transaction_id 
     INNER JOIN sys.databases AS DB 
         ON TDT.database_id = DB.database_id 
     LEFT JOIN sys.dm_tran_session_transactions  AS TST 
         ON TAT.transaction_id = TST.transaction_id 
     LEFT JOIN sys.dm_exec_requests AS REQ 
         ON TAT.transaction_id = REQ.transaction_id 
     LEFT JOIN sys.dm_exec_sessions AS SES 
         ON REQ.session_id = SES.session_id 
     CROSS APPLY sys.dm_exec_sql_text(REQ.sql_handle) AS EST 
WHERE TAT.transaction_id > 255                  -- No system transactions 
      AND ISNULL(REQ.session_id, -1) <> @@SPID  -- Exclude my session 
      -- optional filter to exclude transaction in TempDB 
      -- remove it to get all transactions 
      AND TDT.database_id <> DB_ID(N'tempdb') 
ORDER BY DatabaseName         
        ,TransBegin 
        ,TransName;