sys.dm_tran_transactions_snapshot (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
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.
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
On SQL Server and SQL Managed Instance, requires VIEW SERVER STATE
permission.
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.
Permissions for SQL Server 2022 and later
Requires VIEW SERVER PERFORMANCE 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
Dynamic Management Views and Functions (Transact-SQL)
Transaction Related Dynamic Management Views and Functions (Transact-SQL)
Atsauksmes
https://aka.ms/ContentUserFeedback.
Drīzumā: 2024. gada laikā mēs pakāpeniski pārtrauksim izmantot “GitHub problēmas” kā atsauksmju par saturu mehānismu un aizstāsim to ar jaunu atsauksmju sistēmu. Papildinformāciju skatiet:Iesniegt un skatīt atsauksmes par