sp_volchanged  Chapter 2: Catalog Stored Procedures

Chapter 1: System Procedures

sp_who

Description

Reports information about all current Adaptive Server users and processes or about a particular user or process.

Syntax

sp_who [loginame | "spid"]

Parameters

loginame

is the Adaptive Server login name of the user you are requesting a report on.

spid

is the number of the process you are requesting a report on. Enclose process numbers in quotes (Adaptive Server expects a char type).

Examples

Example 1

Reports on the processes running on Adaptive Server. Process 11 (a select on a table) is blocked by process 8 (a begin transaction followed by an insert on the same table). For process 8, the current loginame is “robert”, but the original loginame is “sa”. Login “sa” executed a set proxy command to impersonate the user “robert”:

sp_who
fid spid  status    loginame origname hostname blk_spid  dbname 
       cmd                blk_xloid
--- ----- --------   -------- -------- -------- -------  ------ 
       ------------------ ---------
  0    1  recv sleep  bird     bird     jazzy    0       master 
       AWAITING COMMAND   0x0000ed92
  0    2  sleeping    NULL     NULL              0       master 
       NETWORK HANDLER    0x0000ed92
  0    3  sleeping    NULL     NULL              0       master 
       MIRROR HANDLER     0x0000ed92
  0    4  sleeping    NULL     NULL              0       master 
       AUDIT PROCESS      0x0000ed92
  0    5  sleeping    NULL     NULL              0       master 
       CHECKPOINT SLEEP   0x0000ed92
  0    6  recv sleep  rose     rose     petal    0       master 
       AWAITING COMMAND   0x0000ed92
  0    7  sleeping    NULL     NULL     actor    0    sybsystemdb
       ASTC HANDLER       0x0000ed92
  0    8  running     robert   sa       helos    0       master 
       SELECT             0x0000ed92
  0    9  send sleep  daisy    daisy    chain    0       pubs2  
       SELECT             0x0000ed92
  0   10  alarm sleep lily     lily     pond     0       master 
       WAITFOR            0x0000ed92
  0   11  lock sleep  viola    viola    cello    8       pubs2  
       SELECT            0x0000ed92

Example 2

Reports on the processes being run by the user “victoria”:

sp_who victoria

Example 3

Reports what Adaptive Server process number 17 is doing:

sp_who "17"

Example 4

Reports on the processes running on Adaptive Server. Although no user processes other than sp_who are running, the server still shows activity. During idle cycles, the housekeeper wash task moves dirty buffers into the buffer wash region, the housekeeper chores task performs other maintenance tasks. The housekeeper garbage collection task , which cleans up data that was logically deleted and resets the rows so that tables have space again, operates at the priority level of the ordinary user.

sp_who
fid spid  status    loginame origname hostname blk_spid dbname
       cmd              block_xloid
---- ---- --------  -------- -------- -------- -------- ------
       ---------------- -----------
  0    1  running   sa       sa       helos    0        master 
       SELECT             0
  0    2  sleeping  NULL     NULL              0        master 
       NETWORK HANDLER    0
  0    3  sleeping  NULL     NULL              0        master 
       DEADLOCK TUNE      0
  0    4  sleeping  NULL     NULL              0        master 
       MIRROR HANDLER     0
  0    5  sleeping  NULL     NULL     actor    0        master
       ASTC HANDLER       0
  0    6  sleeping  NULL     NULL              0        master 
       CHECKPOINT SLEEP   0
  0    5  sleeping  NULL     NULL              0        master 
       HK WASH            0
  0    5  sleeping  NULL     NULL              0        master 
       HK GC              0
  0    5  sleeping  NULL     NULL              0        master 
       HK CHORES           0
  0    5  sleeping  NULL     NULL              0        master 
       HK WASH            0

Example 5

Reports on a system-induced rollback, either of a transaction or a command.

sp_who
fid spid  status    loginame origname hostname blk_spid dbname 
       cmd              block_xloid
  0   11  runnable   sa       sa     copperhead  0         db  
     ROLLBACK                0

Usage

Permissions

Any user can execute sp_who.

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

Commands kill

System procedures sp_familylock, sp_lock





Copyright © 2005. Sybase Inc. All rights reserved. Chapter 2: Catalog Stored Procedures

View this book as PDF