Provides information about current Microsoft® SQL Server™ users and processes. The information returned can be filtered to return only those processes that are not idle.
sp_who [[@login_name =] 'login']
0 (success) or 1 (failure)
| Column | Data type | Description |
|---|---|---|
| spid | smallint | The system process ID. |
| status | nchar(30) | The process status. |
| loginame | nchar(128) | The login name associated with the particular process. |
| hostname | nchar(128) | The host or computer name for each process. |
| blk | char(5) | The system process ID for the blocking process, if one exists. Otherwise, this column is zero. |
| dbname | nchar(128) | The database used by the process. |
| cmd | nchar(16) | The SQL Server command (Transact-SQL statement, SQL Server internal engine process, and so on) executing for the process. |
A blocking process (which may have an exclusive lock) is one that is holding resources that another process needs.
Execute permissions default to the public role.
This example uses sp_who without parameters to report all current users. In this example, process ID 11 (a SELECT on a table) is blocked by process ID 10 (an uncommitted transaction).
USE master
EXEC sp_who
Here is the result set:
spid status loginame hostname blk dbname cmd
----- --------- -------- --------- ----- ---------- ----------------
1 sleeping sa 0 master MIRROR HANDLER
2 sleeping sa 0 master LAZY WRITER
3 sleeping sa 0 master CHECKPOINT SLEEP
4 sleeping sa 0 master RA MANAGER
10 sleeping janetl SEATTLE1 0 pubs AWAITING COMMAND
11 runnable stevenb LONDON2 10 pubs SELECT
This example shows how to view information about a single current user by login name.
USE master
EXEC sp_who 'janetl'
USE master
EXEC sp_who 'active'
USE master
EXEC sp_who '10' --specifies the process_id
| KILL | System Stored Procedures |
| sp_lock |