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
transaction_sequence_num column identifies the transaction sequence (XSN) number of the current snapshot transactions. The output shows two:
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.
|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.|
On SQL Server, requires
VIEW SERVER STATE permission.
On SQL Database, requires the
VIEW DATABASE STATE permission in the database.
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.