Reports information about all current Adaptive Server users and processes or about a particular user or process.
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. 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
Reports on the processes being run by the user “victoria”:
sp_who victoria
Reports what Adaptive Server process number 17 is doing:
sp_who "17"
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
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
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.
Any user can execute sp_who.
Commands kill
System procedures sp_familylock, sp_lock
