| title | sys.dm_tran_database_transactions (Transact-SQL) | ||||
|---|---|---|---|---|---|
| description | sys.dm_tran_database_transactions returns information about transactions at the database level. | ||||
| author | rwestMSFT | ||||
| ms.author | randolphwest | ||||
| ms.date | 11/18/2025 | ||||
| ms.service | sql | ||||
| ms.subservice | system-objects | ||||
| ms.topic | reference | ||||
| ms.custom |
|
||||
| f1_keywords |
|
||||
| helpviewer_keywords |
|
||||
| dev_langs |
|
||||
| monikerRange | >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric || =fabric-sqldb |
[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw-fabricsqldb]
The sys.dm_tran_database_transactions dynamic management view returns information about transactions at the database level.
| 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. |
database_id |
int | ID of the database associated with the transaction. In [!INCLUDE ssazure-sqldb], the values are unique within a single database or an elastic pool, but not within a logical server. |
database_transaction_begin_time |
datetime | Time at which the database became involved in the transaction. Specifically, it is the time of the first log record in the database for the transaction. |
database_transaction_type |
int | 1 = Read/write transaction 2 = Read-only transaction 3 = System transaction |
database_transaction_state |
int | 1 = The transaction has not been initialized. 3 = The transaction has been initialized but has not generated any log records. 4 = The transaction has generated log records. 5 = The transaction has been prepared. 10 = The transaction has been committed. 11 = The transaction has been rolled back. 12 = The transaction is being committed. (The log record is being generated, but is not materialized or persisted.) |
database_transaction_status |
int | [!INCLUDEssInternalOnly] |
database_transaction_status2 |
int | [!INCLUDEssInternalOnly] |
database_transaction_log_record_count |
bigint | Applies to: [!INCLUDEsql2008-md] and later versions. Number of log records generated in the database for the transaction. |
database_transaction_replicate_record_count |
int | Applies to: [!INCLUDEsql2008-md] and later versions. Number of log records generated in the database for the transaction that is replicated. |
database_transaction_log_bytes_used |
bigint | Applies to: [!INCLUDEsql2008-md] and later versions. Number of bytes used so far in the database log for the transaction. |
database_transaction_log_bytes_reserved |
bigint | Applies to: [!INCLUDEsql2008-md] and later versions. Number of bytes reserved for use in the database log for the transaction. |
database_transaction_log_bytes_used_system |
int | Applies to: [!INCLUDEsql2008-md] and later versions. Number of bytes used so far in the database log for system transactions on behalf of the transaction. |
database_transaction_log_bytes_reserved_system |
int | Applies to: [!INCLUDEsql2008-md] and later versions. Number of bytes reserved for use in the database log for system transactions on behalf of the transaction. |
database_transaction_begin_lsn |
numeric(25,0) | Applies to: [!INCLUDEsql2008-md] and later versions. Log sequence number (LSN) of the begin record for the transaction in the database log. |
database_transaction_last_lsn |
numeric(25,0) | Applies to: [!INCLUDEsql2008-md] and later versions. LSN of the most recently logged record for the transaction in the database log. |
database_transaction_most_recent_savepoint_lsn |
numeric(25,0) | Applies to: [!INCLUDEsql2008-md] and later versions. LSN of the most recent savepoint for the transaction in the database log. |
database_transaction_commit_lsn |
numeric(25,0) | Applies to: [!INCLUDEsql2008-md] and later versions. LSN of the commit log record for the transaction in the database log. |
database_transaction_last_rollback_lsn |
numeric(25,0) | Applies to: [!INCLUDEsql2008-md] and later versions. LSN that was most recently rolled back to. If no rollback has taken place, the value is MaxLSN. |
database_transaction_next_undo_lsn |
numeric(25,0) | Applies to: [!INCLUDEsql2008-md] and later versions. LSN of the next record to undo. |
pdw_node_id |
int | Applies to: [!INCLUDEssazuresynapse-md], [!INCLUDEssPDW] The identifier for the node that this distribution is on. |
database_transaction_first_repl_lsn |
numeric(25,0) | Applies to: [!INCLUDEssazuresynapse-md], [!INCLUDEssPDW] LSN of the first log record in the transaction that needs replication. |
On [!INCLUDEssNoVersion_md] 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_database_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.
Requires VIEW SERVER PERFORMANCE STATE permission on the server.
To call this DMV from [!INCLUDEssazuresynapse-md] or [!INCLUDEssPDW], use the name sys.dm_pdw_nodes_tran_database_transactions [!INCLUDEsynapse-analytics-od-unsupported-syntax]