sp_transactions

Syntax

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

Parameters

xid_value – is a transaction name from the xactname column of master.dbo.systransactions.
gtrid_value – is the global transaction ID name for a transaction coordinated by the SAP ASE server.

Examples

Example 1 – 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

Example 2 – 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

Example 3 – Displays general information about transactions that are in the “prepared” state:
```
sp_transactions "state", "prepared"
```
Example 4 – Displays only the transaction names of transactions that are in the “prepared” state:
```
sp_transactions "state", "prepared", "xactname"
```

Example 5 – 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

Usage

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:

Column	Description
`xactkey`	The column shows the internal transaction key that the SAP ASE server uses to identify the transaction.
`type`	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.
`coordinator`	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.
`starttime`	The column indicates the time that the transaction started.
`state`	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.
`connection`	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.
`dbid`	The column indicates the database ID of the database in which transaction started.
`spid`	The column indicates the server process ID associated with the transaction. If the transaction is “Detached,” the “spid” value is 0.
`loid`	The column indicates the unique lock owner ID from `master.dbo.systransactions`.
`failover`	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.
`srvname`	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.
`namelen`	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`.
`gtrid`	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.
`commit_node`	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.
`parent_node`	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.

Permissions

Any user can execute sp_transactions. Permission checks do not differ based on the granular permissions settings.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Information	Values
Event	38
Audit option	exec_procedure
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