Edit

Share via

sys.dm_tran_active_transactions (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric SQL database in Microsoft Fabric

The sys.dm_tran_active_transactions dynamic management view returns information about transactions for the instance.

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. The Microsoft distributed transaction coordinator (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 is not started.

2 = The transaction is active.

3 = The transaction has ended. Used for read-only transactions.

4 = The commit process has been initiated on the distributed transaction. 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.

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.

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.

Permissions

On SQL Server and SQL Managed Instance, requires VIEW SERVER STATE permission.

In Microsoft Fabric, membership in the Contributor workspace role or more privileged role is needed to query sys.dm_tran_active_transactions.

On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Microsoft Entra 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.

Remarks

To call this dynamic management view 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.

Examples

A. Use sys.dm_tran_active_transactions with other DMVs to find information about active transactions

The following example shows any active transactions on the system. The query 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;

Related content

  • Last updated on