sys.dm_tran_transactions_snapshot (Transact-SQL)

Returns a virtual table for the sequence_number of transactions that are active when each snapshot transaction starts. The information that is returned by this view can you help you do the following:

• Find the number of currently active snapshot transactions.

• Identify data modifications that are ignored by a particular snapshot transaction. For a transaction that is active when a snapshot transaction starts, all data modifications by that transaction, even after that transaction commits, are ignored by the snapshot transaction.

For example, consider the following output from sys.dm_tran_transactions_snapshot:

transaction_sequence_num snapshot_id snapshot_sequence_num
------------------------ ----------- ---------------------
59                       0           57
59                       0           58
60                       0           57
60                       0           58
60                       0           59
60                       3           57
60                       3           58
60                       3           59
60                       3           60

The transaction_sequence_num column identifies the transaction sequence (XSN) number of the current snapshot transactions. The output shows two: 59 and 60. The snapshot_sequence_num column identifies the transaction sequence number of the transactions that are active when each snapshot transaction starts.

The output shows that snapshot transaction XSN-59 starts while two active transactions, XSN-57 and XSN-58, are running. If XSN-57 or XSN-58 makes data modifications, XSN-59 ignores the changes and uses row versioning to maintain a transactionally consistent view of the database.

Snapshot transaction XSN-60 ignores data modifications made by XSN-57 and XSN-58 and also XSN 59.

Syntax

dm_tran_transactions_snapshot

Table Returned

Column name	Data type	Description
transaction_sequence_num	bigint	Transaction sequence number (XSN) of a snapshot transaction.
snapshot_id	int	Snapshot ID for each Transact-SQL statement started under read-committed using row versioning. This value is used to generate a transactionally consistent view of the database supporting each query that is being run under read-committed using row versioning.
snapshot_sequence_num	bigint	Transaction sequence number of a transaction that was active when the snapshot transaction started.

Permissions

Requires VIEW SERVER STATE permission on the server.

Remarks

When a snapshot transaction starts, the Database Engine records all of the transactions that are active at that time. sys.dm_tran_transactions_snapshot reports this information for all currently active snapshot transactions.

Each transaction is identified by a transaction sequence number that is assigned when the transaction begins. Transactions start at the time a BEGIN TRANSACTION or BEGIN WORK statement is executed. However, the Database Engine assigns the transaction sequence number with the execution of the first Transact-SQL statement that accesses data after the BEGIN TRANSACTION or BEGIN WORK statement. The transaction sequence numbers are incremented by one.

See Also

Reference

Dynamic Management Views and Functions (Transact-SQL)

Transaction Related Dynamic Management Views and Functions (Transact-SQL)