sys-dm-tran-current-transaction-transact-sql.md

title	sys.dm_tran_current_transaction (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	03/15/2017
ms.prod	sql-non-specified
ms.reviewer
ms.suite
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	sys.dm_tran_current_transaction sys.dm_tran_current_transaction_TSQL dm_tran_current_transaction_TSQL dm_tran_current_transaction
dev_langs	TSQL
helpviewer_keywords	sys.dm_tran_current_transaction dynamic management view
ms.assetid	75d5697d-b390-4963-99b8-fa0b4244a40c
caps.latest.revision	37
author	BYHAM
ms.author	rickbyh
manager	jhubbard

sys.dm_tran_current_transaction (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all_md]

Returns a single row that displays the state information of the transaction in the current session.

Note

To call this from [!INCLUDEssSDWfull] or [!INCLUDEssPDW], use the name sys.dm_pdw_nodes_tran_current_transaction.

Syntax

  
sys.dm_tran_current_transaction  

Table Returned

Column name	Data type	Description
transaction_id	bigint	Transaction ID of the current snapshot.
transaction_sequence_num	bigint	Sequence number of the transaction that generates the record version.
transaction_is_snapshot	bit	Snapshot isolation state. This value is 1 if the transaction is started under snapshot isolation. Otherwise, the value is 0.
first_snapshot_sequence_num	bigint	Lowest transaction sequence number of the transactions that were active when a snapshot was taken. On execution, a snapshot transaction takes a snapshot of all of the active transactions at that time. For nonsnapshot transactions, this column shows 0.
last_transaction_sequence_num	bigint	Global sequence number. This value represents the last transaction sequence number that was generated by the system.
first_useful_sequence_num	bigint	Global sequence number. This value represents the oldest transaction sequence number of the transaction that has row versions that must be retained in the version store. Row versions that were created by prior transactions can be removed.
pdw_node_id	int	Applies to: [!INCLUDEssSDWfull], [!INCLUDEssPDW] The identifier for the node that this distribution is on.

Permissions

On [!INCLUDEssNoVersion] requires VIEW SERVER STATE permission on the server.

On [!INCLUDEssSDS] Premium Tiers requires the VIEW DATABASE STATE permission in the database. On [!INCLUDEssSDS] Standard and Basic Tiers requires the [!INCLUDEssSDS] admin account.

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 each transaction.

SELECT   
    transaction_id  
   ,transaction_sequence_num  
   ,transaction_is_snapshot  
   ,first_snapshot_sequence_num  
   ,last_transaction_sequence_num  
   ,first_useful_sequence_num  
  FROM sys.dm_tran_current_transaction;  

Here is the result for XSN-59.

transaction_id       transaction_sequence_num transaction_is_snapshot  
-------------------- ------------------------ -----------------------  
9387                 59                       1                         
  
first_snapshot_sequence_num last_transaction_sequence_num  
--------------------------- -----------------------------  
57                               61                        
  
first_useful_sequence_num  
-------------------------  
57  

The output shows that XSN-59 is a snapshot transaction that uses XSN-57 as the first transaction that was active when XSN-59 started. This means that XSN-59 reads data committed by transactions that have a transaction sequence number lower than XSN-57.

Here is the result for XSN-57.

transaction_id       transaction_sequence_num transaction_is_snapshot  
-------------------- ------------------------ -----------------------  
9295                 57                       0  
  
first_snapshot_sequence_num last_transaction_sequence_num  
--------------------------- -----------------------------  
NULL                        61  
  
first_useful_sequence_num  
-------------------------  
57  

Because XSN-57 is not a snapshot transaction, first_snapshot_sequence_num is NULL.

See Also

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