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-aa01f04ebb9a-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-aa01f04ebb9a-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.

See also

System procedures sp_lock, sp_who