Viewing active transactions with sp_transactions

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