Viewing active transactions with sp

The sp_transactions procedure translates information from systransactions and syscoordinations to provide information about active transactions.

When used without keywords, sp_transactions displays information about all active transactions:

sp_transactions

xactkey                        type        coordinator starttime
 state             connection dbid   spid   loid
 failover                   srvname                        namelen
 xactname
 ------------------------------ ----------- ----------- 
 --------------------
 ----------------- ---------- ------ ------ -----------
 -------------------------- ------------------------------ -------
 -----------------------------------------------------------------
 0x00000b1700040000dd6821390001 Local       None        Jun 1 1999 3:47PM
 Begun             Attached        1   1      2
 Resident Tx                NULL                                17
 $user_transaction
 0x00000b1700040000dd6821390001 Remote      ASTC        Jun 1 1999 3:47PM
 Begun             NA              0   8      0
 Resident Tx                caserv2                          108
 
 00000b1700040000dd6821390001-aa01f04ebb9a-00000b1700040000dd6821390001-aa01f04ebb9a-caserv1-caserv1-0002

Identify Local, Remote, and External Transactions
The type column of systransactions indicates whether the transaction is local, remote, or external.
Identify the Transaction Coordinator
The coordinator column of systransactions indicates the method or protocol used to manage a transaction.
View the Transaction Thread of Control
The spid column of systransactions displays the Process ID of the process attached to the transaction (or 0 if the transaction is detached from its thread of control).
Understanding Transaction State Information
The state column of systransactions displays information about the current state of each transaction.
Limiting sp_transactions Output to Specific States
Use sp_transactions with the state keyword to limit output to the specified transaction state.
Transaction Failover Information
The failover column displays special information for servers operating in high availability environments.

Parent topic: Getting Information About Distributed Transactions

Viewing active transactions with sp_transactions