sp_thresholdaction  sp_unbindcache

Chapter 1: System Procedures

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





Copyright © 2005. Sybase Inc. All rights reserved. sp_unbindcache

View this book as PDF