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.
sp_who does not include the threadpool column.
sp_who [loginame | "spid"]
is the Adaptive Server login name of the user you are requesting a report on.
is the number of the process you are requesting a report on. Enclose process numbers in quotes (Adaptive Server expects a char type).
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
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
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
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
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
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. |
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 |
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.
Any user can execute sp_who.
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 |
|
Commands kill
System procedures sp_familylock, sp_lock
