sp_transactions

Description

Reports information about active 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 Adaptive 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


Column descriptions for sp_transactions output

Permissions

Any user can execute sp_transactions.

Auditing

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

  • 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

See also

System procedures sp_lock, sp_who