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 task moves dirty buffers into the buffer wash region:

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 
       HOUSEKEEPER       0

Usage

sp_who reports information about a specified user or Adaptive Server process.
Without parameters, sp_who reports which users are running what processes in all databases.
The columns returned by sp_who are:
- fid – identifies the family (including the coordinating process and its worker processes) to which a lock belongs. For more information, see sp_familylock.
- spid – identifies the process number. A System Administrator can use this number with the Transact-SQL kill command to stop the process.
- status – indicates whether the process is running or sleeping.
- loginame – the login or alias of the user who started the process. For all system processes, loginame is NULL.
- origname – If the loginame is an alias, origname shows the real login name. If not, origname shows the same information as loginame.
- hostname – the name of the server on which the database resides.
- blk_spid – contains the process IDs of the blocking process, if there is one. A blocking process (which may be infected or have an exclusive lock) is one that is holding resources needed by another process.
- dbname – indicates the name of the database on which the process is running.
- cmd – identifies the command or process currently being executed. Evaluation of a conditional statement, such as an if or while loop, returns cond.
- block_xloid – identifies the unique lock owner ID of a blocking transaction.
Running sp_who on a single-engine server shows the sp_who process currently running and all other processes that are runnable or in one of the sleep states. In multi-engine servers, there can be a “running” process for each engine.
If you enable mirrored disks or remote procedure calls, the mirror handler and the site handler also appear in the report from sp_who.

Permissions

Any user can execute sp_who.