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

Examples

Example 1

Displays temporary database names of all active sessions under the tempdbname column:

 sp_who

fid spid status loginame origname hostname blk_spid dbname tempdbname cmd block_xloid
--- ---- ------ ------- -------- -------- -------- ------ ---------- --- ----------
  0    2 sleeping NULL   NULL     NULL           0 master tempdb DEADLOCK TUNE    0
  0    3 sleeping NULL   NULL     NULL           0 master tempdb SHUTDOWN HANDLER 0
  0    4 sleeping NULL   NULL     NULL           0 master tempdb ASTC HANDLER     0
  0    5 sleeping NULL   NULL     NULL           0 master tempdb CHECKPOINT SLEEP 0

Example 2

Reports on the processes running on Adaptive Server. Process 11 (a select into 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 INSERT            0x0000ed92

Example 3

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

sp_who victoria

Example 4

Reports what Adaptive Server process number 17 is doing:

sp_who "17"

Example 5

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 6

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

• 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:

  Column	Description
  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.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

See also