Using sp_transactions with the xid keyword displays the commit node, parent node, and gtrid of a particular transaction, in addition to the output described under “Viewing active transactions with sp_transactions”. This form of sp_transactions requires that you specify a particular transaction name. For example:
sp_transactions "xid", "00000b1700040000dd6821390001-aa01f04ebb9a-00000b1700040000dd6821390001-aa01f04ebb9a-caserv1-caserv1-0002"
xactkey type coordinator starttime state connection dbid spid loid failover srvname namelen xactname commit_node parent_node gtrid ------------------------------ ----------- ----------- -------------------- ----------------- ---------- ------ ------ ----------- -------------------------- ------------------------------ ------- ----------------------------------------------------------------- ------------- ------------- ------------- 0x00000b2500080000dd6821960001 External ASTC Jun 1 1999 3:47PM Begun Attached 1 8 139 Resident Tx NULL 108 00000b1700040000dd6821390001-aa01f04ebb9a-00000b1700040000dd6821390001-aa01f04ebb9a-caserv1-caserv1-0002 caserv1 caserv1 00000b1700040000dd6821390001-aa01f04ebb9a
For distributed transactions coordinated by Adaptive Server, the “commit node” column lists the name of the server that executes the topmost branch of the distributed transaction. This transaction determines the commit or rollback status for all branches of the transaction. See “Hierarchical transaction coordination” for more information.
The “parent node” column lists the name of the server that initiated the transaction. In the sp_transactions output above, the “commit node” and “parent node” columns list the same server, caserv1. This indicates that the distributed transaction originated on caserv1, and caserv1 propagated a branch of the transaction to the current server.
The “gtrid” column displays the global transaction ID for distributed transactions coordinated by Adaptive Server. Transaction branches that are part of the same distributed transaction share the same gtrid. You can use a specific gtrid with the sp_transactions gtrid keyword to determine the state of other transaction branches running on the current server. This is useful for System Administrators who must determine whether a particular branch of a distributed transaction should be heuristically committed or rolled back. See “Determining the commit status for Adaptive Server transactions” for an example that uses sp_transactions with the gtrid keyword.
For transactions coordinated by an X/Open XA-compliant transaction manager, MSDTC, or SYB2PC, the gtrid column shows the full transaction name supplied by the external coordinator.