sys.dm_tran_current_snapshot (Transact-SQL)
Returns a virtual table that displays all active transactions at the time when the current snapshot transaction starts. If the current transaction is not a snapshot transaction, this function returns no rows. sys.dm_tran_current_snapshot is similar to sys.dm_tran_transactions_snapshot, except that sys.dm_tran_current_snapshot returns only the active transactions for the current snapshot transaction.
Syntax
sys.dm_tran_current_snapshot
Table Returned
Column name |
Data type |
Description |
|---|---|---|
transaction_sequence_num |
bigint |
Transaction sequence number of the active transaction. |
Permissions
Requires VIEW SERVER STATE permission on the server.
Examples
The following example uses a test scenario in which four concurrent transactions, each identified by a transaction sequence number (XSN), are running in a database that has the ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT options set to ON. The following transactions are running:
XSN-57 is an update operation under serializable isolation.
XSN-58 is the same as XSN-57.
XSN-59 is a select operation under snapshot isolation.
XSN-60 is the same as XSN-59.
The following query is executed within the scope of XSN-59.
SELECT
transaction_sequence_num
FROM sys.dm_tran_current_snapshot;
Here is the result set.
transaction_sequence_num
------------------------
57
58
The results show that XSN-57 and XSN-58 were active at the time that the snapshot transaction XSN-59 started. This same result persists, even after XSN-57 and XSN-58 commit or roll back, until the snapshot transaction finishes.
The same query is executed within the scope of XSN-60.
Here is the result set.
transaction_sequence_num
------------------------
57
58
59
The output for XSN-60 includes the same transactions that appear for XSN-59, but also includes XSN-59, which was active when XSN-60 started.