sp_who

Description

Reports information about all current Adaptive Server users and processes or about a particular user or process. Includes the thread_pool column, which describes the thread pool Adaptive Server uses to execute a task.

Considerations for process mode

sp_who does not include the threadpool column.

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. 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
   tempdbname   cmd              block_xloid    threadpool
--- ----  --------- --------- ---------  ----------------   --------  -----------
   ----------- ----------------- -----------    -----------------
  0    2   sleeping      NULL      NULL              NULL          0       master
         tempdb     DEADLOCK TUNE           0    syb_default_pool
  0    3   sleeping      NULL      NULL              NULL          0       master
   tempdb            ASTC HANDLER           0    syb_default_pool
  0    4   sleeping      NULL      NULL              NULL          0       master
   tempdb        CHECKPOINT SLEEP           0    syb_default_pool
  0    5   sleeping      NULL      NULL              NULL          0       master
   tempdb                 HK WASH           0    syb_default_pool
  0    6   sleeping      NULL      NULL              NULL          0       master
   tempdb                   HK GC           0    syb_default_pool
  0    7   sleeping      NULL      NULL              NULL          0       master
   tempdb               HK CHORES           0    syb_default_pool
  0    8   sleeping      NULL      NULL              NULL          0       master
   tempdb            PORT MANAGER           0    syb_default_pool
  0    9   sleeping      NULL      NULL              NULL          0       master
   tempdb         NETWORK HANDLER           0    syb_default_pool
  0   10   sleeping      NULL      NULL              NULL          0       master
   tempdb       LICENSE HEARTBEAT           0    syb_default_pool
  0   13   sleeping      NULL      NULL              NULL          0       master
   tempdb         NETWORK HANDLER           0    syb_default_pool
  0   14   sleeping      NULL      NULL              NULL          0       master
   tempdb         NETWORK HANDLER           0    syb_default_pool
  0   17   sleeping      NULL      NULL              NULL          0       master
   tempdb         NETWORK HANDLER           0    syb_default_pool
  0   20   sleeping      NULL      NULL              NULL          0       master
   tempdb         NETWORK HANDLER           0    syb_default_pool
  0   26   running         sa        sa  tiger.sybase.com          0       master
   tempdb                     INSERT            0     syb_default_pool

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
   tempdbname  cmd               block_xloid    threadpool
--- ----  ---------   ---------  ---------  ---------------   --------  -----------
   ----------- ----------------- -----------    -----------------
  0    1 recv sleep        bird       bird            jazzy          0       master
        tempdb  AWAITING COMMAND           0     syb_default_pool
  0    2   sleeping        NULL       NULL                           0       master
        tempdb  NETWORK HANDLER            0     syb_default_pool
  0    3   sleeping        NULL       NULL                           0       master
        tempdb  MIRROR HANDLER             0     syb_default_pool
  0    4   sleeping        NULL       NULL                           0       master
        tempdb  AUDIT PROCESS              0     syb_default_pool
  0    5   sleeping        NULL       NULL                           0       master
        tempdb  CHECKPOINT SLEEP           0     syb_default_pool
  0    6 recv sleep        rose                   rose petal         0       master
        tempdb  AWAITING COMMAND           0     syb_default_pool
  0    7   sleeping        NULL       NULL             actor         0  sybsystemdb
        tempdb  ASTC HANDLER               0     syb_default_pool
  0    8    running      robert         sa             helos         0       master
        tempdb  SELECT                     0     syb_default_pool
  0    9  send sleep      daisy      daisy             chain         0        pubs2
        tempdb  SELECT                     0     syb_default_pool
  0   10 alarm sleep       lily       lily              pond         0       master
        tempdb  WAITFOR                    0     syb_default_pool
  0   11  lock sleep      viola      viola             cello         8        pubs2
        tempdb  INSERT                     0     syb_default_pool

Example 3

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

sp_who joe
fid spid  status   loginame   origname   hostname           blk_spid  dbname
   tempdbname   cmd              block_xloid    threadpool
--- ----  --------- --------- ---------  ----------------   --------  -----------
   ----------- ----------------- -----------    -----------------
  0   28 recv sleep       joe       joe  tiger.sybase.com          0        pubs2
         tempdb          SELECT           0           syb_default_pool

Example 4

Reports what Adaptive Server process number 17 is doing:

sp_who "17"
fid spid  status   loginame   origname   hostname           blk_spid  dbname
   tempdbname   cmd              block_xloid    threadpool
--- ----  --------- --------- ---------  ----------------   --------  -----------
   ----------- ----------------- -----------    -----------------
  0   17   sleeping      NULL      NULL              NULL          0        pubs2
         tempdb     NETWORK HANDLER            0       syb_default_pool

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
   tempdbname   cmd              block_xloid    threadpool
--- ----  --------- --------- ---------  ----------------   --------  -----------
   ----------- ----------------- -----------    -----------------
  0   28    running       joe       joe  tiger.sybase.com          0        pubs2
         tempdb         rollback               0       syb_default_pool

Usage

Permissions

Any user can execute sp_who. Permission checks do not differ based on the granular permissions settings.

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