sp_who

Reports information about all current SAP ASE users and processes or about a particular user or process. Includes the thread_pool column, which describes the thread pool the SAP ASE 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 SAP ASE 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 (the SAP ASE server expects a char type).

Examples

Example 1 – Reports on the processes running on the SAP ASE 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 the SAP ASE 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 the SAP ASE 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

There are additional considerations when using sp_who:

sp_who reports information about a specified user or the SAP ASE 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.
- tempdb – Temporary database assigned to the session.
- 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.
- threadpool – Thread pool the task uses.
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 multiengine 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.

See also kill in Reference Manual: Commands.

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:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	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