sys.dm_tran_database_transactions (Transact-SQL)

sys.dm_tran_database_transactions (Transact-SQL)

 

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Returns information about transactions at the database level.

System_CAPS_ICON_note.jpg Note


To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_tran_database_transactions.

Column nameData typeDescription
transaction_idbigintID 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_idintID of the database associated with the transaction.
database_transaction_begin_timedatetimeTime 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_typeint1 = Read/write transaction

2 = Read-only transaction

3 = System transaction
database_transaction_stateint1 = 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. In this state the log record is being generated, but it has not been materialized or persisted.
database_transaction_statusintIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
database_transaction_status2intIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
database_transaction_log_record_countbigintApplies to: SQL Server 2008 through SQL Server 2016.

Number of log records generated in the database for the transaction.
database_transaction_replicate_record_countintApplies to: SQL Server 2008 through SQL Server 2016.

Number of log records generated in the database for the transaction that will be replicated.
database_transaction_log_bytes_usedbigintApplies to: SQL Server 2008 through SQL Server 2016.

Number of bytes used so far in the database log for the transaction.
database_transaction_log_bytes_reservedbigintApplies to: SQL Server 2008 through SQL Server 2016.

Number of bytes reserved for use in the database log for the transaction.
database_transaction_log_bytes_used_systemintApplies to: SQL Server 2008 through SQL Server 2016.

Number of bytes used so far in the database log for system transactions on behalf of the transaction.
database_transaction_log_bytes_reserved_systemintApplies to: SQL Server 2008 through SQL Server 2016.

Number of bytes reserved for use in the database log for system transactions on behalf of the transaction.
database_transaction_begin_lsnnumeric(25,0)Applies to: SQL Server 2008 through SQL Server 2016.

Log sequence number (LSN) of the begin record for the transaction in the database log.
database_transaction_last_lsnnumeric(25,0)Applies to: SQL Server 2008 through SQL Server 2016.

LSN of the most recently logged record for the transaction in the database log.
database_transaction_most_recent_savepoint_lsnnumeric(25,0)Applies to: SQL Server 2008 through SQL Server 2016.

LSN of the most recent savepoint for the transaction in the database log.
database_transaction_commit_lsnnumeric(25,0)Applies to: SQL Server 2008 through SQL Server 2016.

LSN of the commit log record for the transaction in the database log.
database_transaction_last_rollback_lsnnumeric(25,0)Applies to: SQL Server 2008 through SQL Server 2016.

LSN that was most recently rolled back to. If no rollback has taken place, the value will be MaxLSN (-1:-1:-1).
database_transaction_next_undo_lsnnumeric(25,0)Applies to: SQL Server 2008 through SQL Server 2016.

LSN of the next record to undo.
pdw_node_idintApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse

The identifier for the node that this distribution is on.

Requires VIEW SERVER STATE permission on the server.

sys.dm_tran_active_transactions (Transact-SQL)
sys.dm_tran_session_transactions (Transact-SQL)
Dynamic Management Views and Functions (Transact-SQL)
Transaction Related Dynamic Management Views and Functions (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft