Share via

sys.dm_tran_current_snapshot

  • Article

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.

See Also

Reference

Dynamic Management Views and Functions
Transaction Related Dynamic Management Views and Functions

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release

History

5 December 2005

Term

Definition

**New content:**Added an example.