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;
Released for:Schneider Electric USA
