sys.dm_tran_database_transactions (Transact-SQL)

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.

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. In this state the log record is being generated, but it has not been materialized or persisted.

database_transaction_status

int

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

database_transaction_status2

int

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

database_transaction_log_record_count

int

Number of log records generated in the database for the transaction.

database_transaction_replicate_record_count

int

Number of log records generated in the database for the transaction that will be replicated.

database_transaction_log_bytes_used

bigint

Number of bytes used so far in the database log for the transaction.

database_transaction_log_bytes_reserved

bigint

Number of bytes reserved for use in the database log for the transaction.

database_transaction_log_bytes_used_system

int

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

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)

Log sequence number (LSN) of the begin record for the transaction in the database log.

database_transaction_last_lsn

numeric(25,0)

LSN of the most recently logged record for the transaction in the database log.

database_transaction_most_recent_savepoint_lsn

numeric(25,0)

LSN of the most recent savepoint for the transaction in the database log.

database_transaction_commit_lsn

numeric(25,0)

LSN of the commit log record for the transaction in the database log.

database_transaction_last_rollback_lsn

numeric(25,0)

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_lsn

numeric(25,0)

LSN of the next record to undo.

Permissions

Requires VIEW SERVER STATE permission on the server.