Reports information about active transactions.


sp_tranactions ["xid", xid_value] | 
	["state", {"heuristic_commit" | "heuristic_abort" 
	| "prepared" | "indoubt"} [, "xactname"]] |
	["gtrid", gtrid_value]




There are additional considerations when using sp_transactions:
  • 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.

The columns for sp_transactions output are:

The column shows the internal transaction key that the SAP ASE server uses to identify the transaction.

The column indicates the type of transaction:
  • “Local” means that the transaction was explicitly started on the local SAP ASE server with a begin transaction statement.

  • “Remote” indicates a transaction executing on a remote SAP ASE server.

  • “External” means that the transaction has an external coordinator associated with it. For example, transactions coordinated by a remote SAP ASE server, MSDTC, or an X/Open XA transaction manager are flagged as “External.”

  • “Dtx_State” is a special state for distributed transactions coordinated by the SAP ASE server. It indicates that a transaction on the local server was either committed or aborted, but the SAP ASE server has been unable to resolve a branch of that transaction on a remote participant. This may happen in cases where the SAP ASE server loses contact with a server it is coordinating.

The column indicates the method or protocol used to manage a distributed transaction. The values for coordinator are:
  • None – transaction is not a distributed transaction and does not require a coordinating protocol.

  • ASTC – transaction is coordinated using the SAP ASE transaction coordination services.

  • XA – transaction is coordinated by the X/Open XA-compliant transaction manager via the SAP ASE 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 column indicates the time that the transaction started.

The column indicates the state of the transaction at the time sp_transactions ran:
  • 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 modifies 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 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 column indicates the database ID of the database in which transaction started.


The column indicates the server process ID associated with the transaction. If the transaction is “Detached,” the “spid” value is 0.


The column indicates the unique lock owner ID from master.dbo.systransactions.

The 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 SAP ASE 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 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 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 the SAP ASE server, the 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 the SAP ASE server, the 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 the SAP ASE server, commit_node displays one of these values:
  • server_name – commit or parent node is an SAP ASE server with the specified server_name.

  • XATM – commit or parent node is an X/Open XA-compliant transaction manager.

  • MSDTCTM – ommit or parent node is MSDTC.

  • SYB2PCTM – transaction is coordinated using SYB2PC protocol.


For transactions coordinated by the SAP ASE server, the column indicates the server that is coordinating the external transaction on the local server.

For transactions not coordinated by the SAP ASE server, parent_node displays the same values as those displayed by commit_node.

Note: The values for commit_node and parent_node can be different, depending on the levels of hierarchy in the distributed transaction.

See also Using Adaptive Server Distributed Transaction Management Features.


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:



Audit option


Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

Related reference