Returns information about transactions for the instance of SQL Server.
To call this from Azure Synapse Analytics or Analytics Platform System (PDW), use the name sys.dm_pdw_nodes_tran_active_transactions. This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.
|Column Name||Data Type||Description|
|transaction_id||bigint||ID of the transaction at the instance level, not the database level. It is only unique across all databases within an instance but not unique across all server instances.|
|name||nvarchar(32)||Transaction name. This is overwritten if the transaction is marked and the marked name replaces the transaction name.|
|transaction_begin_time||datetime||Time that the transaction started.|
|transaction_type||int||Type of transaction.
1 = Read/write transaction
2 = Read-only transaction
3 = System transaction
4 = Distributed transaction
|transaction_uow||uniqueidentifier||Transaction unit of work (UOW) identifier for distributed transactions. MS DTC uses the UOW identifier to work with the distributed transaction.|
|transaction_state||int||0 = The transaction has not been completely initialized yet.
1 = The transaction has been initialized but has not started.
2 = The transaction is active.
3 = The transaction has ended. This is used for read-only transactions.
4 = The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.
5 = The transaction is in a prepared state and waiting resolution.
6 = The transaction has been committed.
7 = The transaction is being rolled back.
8 = The transaction has been rolled back.
|transaction_status||int||Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.|
|transaction_status2||int||Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.|
|dtc_state||int||Applies to: Azure SQL Database (Initial release through current release).
1 = ACTIVE
2 = PREPARED
3 = COMMITTED
4 = ABORTED
5 = RECOVERED
|dtc_status||int||Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.|
|dtc_isolation_level||int||Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.|
|filestream_transaction_id||varbinary(128)||Applies to: Azure SQL Database (Initial release through current release).
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
|pdw_node_id||int||Applies to: Azure Synapse Analytics, Analytics Platform System (PDW)
The identifier for the node that this distribution is on.
On SQL Server and SQL Managed Instance, requires
VIEW SERVER STATE permission.
On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Azure Active Directory admin account, or membership in the
##MS_ServerStateReader## server role is required. On all other SQL Database service objectives, either the
VIEW DATABASE STATE permission on the database, or membership in the
##MS_ServerStateReader## server role is required.
Permissions for SQL Server 2022 and later
Requires VIEW SERVER PERFORMANCE STATE permission on the server.
A. Using sys.dm_tran_active_transactions with other DMVs to find information about active transactions
The following example shows any active transactions on the system and provides detailed information about the transaction, the user session, the application that submitted, and the query that started it and many others.
SELECT GETDATE() as now, DATEDIFF(SECOND, transaction_begin_time, GETDATE()) as tran_elapsed_time_seconds, st.session_id, txt.text, * FROM sys.dm_tran_active_transactions at INNER JOIN sys.dm_tran_session_transactions st ON st.transaction_id = at.transaction_id LEFT OUTER JOIN sys.dm_exec_sessions sess ON st.session_id = sess.session_id LEFT OUTER JOIN sys.dm_exec_connections conn ON conn.session_id = sess.session_id OUTER APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle) AS txt ORDER BY tran_elapsed_time_seconds DESC;
Dynamic Management Views and Functions (Transact-SQL)
Transaction Related Dynamic Management Views and Functions (Transact-SQL)