Reports information about active transactions.
sp_tranactions ["xid", xid_value] |
["state", {"heuristic_commit" | "heuristic_abort"
| "prepared" | "indoubt"} [, "xactname"]] |
["gtrid", gtrid_value]
is a transaction name from the xactname column of master.dbo.systransactions.
is the global transaction ID name for a transaction coordinated by Adaptive Server.
Displays general 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-aa0 1f04ebb9a-caserv1-caserv1-0002
Displays detailed information for the specified transaction:
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-aa0 1f04ebb9a-caserv1-caserv1-0002 caserv1 caserv1 00000b1700040000dd6821390001-aa01f04ebb9a
Displays general information about transactions that are in the “prepared” state:
sp_transactions "state", "prepared"
Displays only the transaction names of transactions that are in the “prepared” state:
sp_transactions "state", "prepared", "xactname"
Displays status information for transactions having the specified global transaction ID:
sp_transactions "gtrid", "00000b1700040000dd6821390001-aa01f04ebb9a"
xactkey type coordinator starttime state connection dbid spid loid failover srvname namelen xactname commit_node parent_node ------------------------------ ------- ---------- ----------------- ----------- ---------- ------ ------ ----------- ------------ --------------- ------- ------------- ------------- 0x00000b1700040000dd6821390001 Local None Jun 1 1999 3:47PM Begun Attached 1 1 2 Resident Tx NULL 17 $user_transaction caserv1 caserv1
sp_transactions translates data from the systransactions table to display information about active transactions. systransactions itself comprises data in the syscoordinations table, as well as in-memory information about active transactions.
sp_transactions with no keywords displays information about all active transactions.
sp_transactions with the xid keyword displays the gtrid, commit_node, and parent_node columns only for the specified transaction.
sp_transactions with the state keyword displays information only for the active transactions in the specified state.
sp_transactions with both xid and xactname displays only the transaction names for transactions in the specified state.
sp_transactions with the gtrid keyword displays information only for the transactions with the specified global transaction ID.
sp_transactions replaces the sp_xa_scan_xact procedure provided with XA-Library and XA-Server products.
See Using Adaptive Server Distributed Transaction Management Features for more information.
The xactkey column shows the internal transaction key that Adaptive Server uses to identify the transaction.
The type column indicates the type of transaction:
“Local” means that the transaction was explicitly started on the local Adaptive Server with a begin transaction statement.
“Remote” indicates a transaction executing on a remote Adaptive Server.
“External” means that the transaction has an external coordinator associated with it. For example, transactions coordinated by a remote Adaptive Server, MSDTC, or an X/Open XA transaction manager are flagged as “External.”
“Dtx_State” is a special state for distributed transactions coordinated by Adaptive Server. It indicates that a transaction on the local server was either committed or aborted, but Adaptive Server has been unable to resolve a branch of that transaction on a remote participant. This may happen in cases where Adaptive Server loses contact with a server it is coordinating.
The coordinator column indicates the method or protocol used to manage a distributed transaction:
sp_transactions “coordinator” value |
Meaning |
|---|---|
None |
Transaction is not a distributed transaction and does not require a coordinating protocol. |
ASTC |
Transaction is coordinated using the Adaptive Server transaction coordination services. |
XA |
Transaction is coordinated by the X/Open XA-compliant transaction manager via the Adaptive Server XA-Library interface. Such transaction managers include Encina, CICS, and Tuxedo. |
DTC |
Transaction is coordinated by MSDTC. |
SYB2PC |
Transaction is coordinated using Sybase two-phase commit protocol. |
The starttime column indicates the time that the transaction started.
The state column indicates the state of the transaction at the time sp_transactions ran:
sp_transactions “state” value |
Meaning |
|---|---|
Begun |
Transaction has begun but no updates have been performed. |
Done Command |
Transaction completed an update command. |
Done |
X/Open XA transaction has finished modifying data. |
Prepared |
Transaction has successfully prepared. |
In Command |
Transaction is currently modifying data. |
In Abort Cmd |
Execution of the current command in the transaction has been aborted. |
Committed |
Transaction has successfully committed, and the commit log record has been written. |
In Post Commit |
Transaction has successfully committed, but is currently deallocating transaction resources. |
In Abort Tran |
Transaction is being aborted. This may happen either as a result of an explicit command, or because of a system failure. |
In Abort Savept |
Transaction is being rolled back to a savepoint. |
Begun-Detached |
Transaction has begun, but there is no thread currently attached to it. |
Done Cmd-Detached |
Transaction has finished modifying data, and no thread is currently attached to it. |
Done-Detached |
Transaction will modify no more data, and no thread is currently attached to it. |
Prepared-Detached |
Transaction has successfully prepared, and no thread is currently attached to it. |
Heur Committed |
Transaction has been heuristically committed using the dbcc complete_xact command. |
Heur Rolledback |
Transaction has been heuristically rolled back using the dbcc complete_xact command. |
The connection column indicates whether or not the transaction is currently associated with a thread:
“Attached” indicates that the transaction has an associated thread of control.
“Detached” indicates that there is no thread currently associated with the transaction. Some external transaction managers, such as CICS and TUXEDO, use the X/Open XA “suspend” and “join” semantics to associate different threads with the same transaction.
The dbid column indicates the database ID of the database in which transaction started.
The spid column indicates the server process ID associated with the transaction. If the transaction is “Detached,” the “spid” value is 0.
The loid column indicates the unique lock owner ID from master.dbo.systransactions.
The failover column indicates the failover state for the transaction:
“Resident Tx” indicates that the transaction started and is executing on the same server. “Resident Tx” is displayed under normal operating conditions, and on systems that do not utilize Adaptive Server high availability features.
“Failed-over Tx” is displayed after there has been a failover to a secondary companion server. “Failed-over Tx” means that a transaction originally started on a primary server and reached the prepared state, but was automatically migrated to the secondary companion server (for example, as a result of a system failure on the primary server). The migration of a prepared transaction occurs transparently to an external coordinating service.
“Tx by Failover-Conn” indicates that there was an attempt to start the transaction on a designated server, but the transaction was instead started on the secondary companion server. This occurs when the original server has experienced a failover condition.
The srvname column indicates the name of the remote server on which the transaction is executing. This column is only meaningful for remote transactions. For local and external transactions, srvname is null.
The namelen column indicates the total length of the xactname value.
xactname is the transaction name. For local transactions, the transaction name may be defined as part of the begin transaction command. External transaction managers supply unique transaction names in a variety of formats. For example, X/Open XA-compliant transaction managers supply a transaction ID (xid) consisting of a global transaction identifier and a branch qualifier, both of which are stored in xactname.
For transactions coordinated by Adaptive Server, the gtrid column displays the global transaction ID. 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 in the same distributed transaction.
sp_transactions cannot display the gtrid for transactions that have an external coordinator. 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.
For transactions coordinated by Adaptive Server, the commit_node column indicates the server that executes the outermost block of the distributed transaction. This outermost block ultimately determines the commit status of all subordinate transactions.
For transactions not coordinated by Adaptive Server, commit_node displays one of the values described in Table 1-34.
Value |
Meaning |
|---|---|
server_name |
Commit or parent node is an Adaptive Server with the specified server_name. |
XATM |
Commit or parent node is an X/Open XA-compliant transaction manager. |
MSDTCTM |
Commit or parent node is MSDTC. |
SYB2PCTM |
Transaction is coordinated using SYB2PC protocol. |
For transactions coordinated by Adaptive Server, the parent_node column indicates the server that is coordinating the external transaction on the local server.
For transactions not coordinated by Adaptive Server, parent_node displays one of the values described in Table 1-34.
The values for commit_node and parent_node can
be different, depending on the levels of hierarchy in the distributed
transaction.
Any user can execute sp_transactions. Permission checks do not differ based on the granular permissions settings.
Values in event and extrainfo columns from the sysaudits table are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
38 |
exec_procedure |
Execution of a procedure |
|
