sp_who
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.
Syntax
sp_who [[@login_name =] 'login']
Arguments
[@login_name =] 'login'
Is a user login name on SQL Server. login is sysname, with a default of NULL. If no name is specified, the procedure reports all active users of SQL Server. login can also be a specific process identification number (SPID). To return information on active processes, specify ACTIVE. ACTIVE excludes from the report processes that are waiting for the next command from the user.
Return Code Values
0 (success) or 1 (failure)
Result Sets
sp_who returns a result set with the following information.
| Column | Data type | Description |
|---|---|---|
| spid | smallint | The system process ID. |
| ecid | smallint | The execution context ID of a given thread associated with a specific SPID.
ECID = {0, 1, 2, 3, ...n}, where 0 always represents the main or parent thread, and {1, 2, 3, ...n} represent the sub-threads. |
| 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.
When a transaction associated with a given spid is blocked by an orphan distributed transaction, this column will return a '-2' for the blocking orphan transaction. |
| 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. |
The sp_who result set will be sorted in ascending order according to the spid values. In case of parallel processing, sub-threads are created for the specific spid. The main thread is indicated as spid =xxx and ecid =0. The other sub-threads have the same spid = xxx, but with ecid > 0. Thus, multiple rows for that spid number will be returned -- grouped together within that spid's placement in the overall list. The sub-threads will be listed in random order, except for the parent thread (ecid = 0), which will be listed first for that spid.
Remarks
A blocking process (which may have an exclusive lock) is one that is holding resources that another process needs.
In SQL Server 2000, all orphaned DTC transactions are assigned the SPID value of '-2'. Orphaned DTC transactions are distributed transactions that are not associated with any SPID. Thus, when an orphaned transaction is blocking another process, this orphaned distributed transaction can be identified by its distinctive '-2' SPID value. For more information, see KILL.
SQL Server 2000 reserves SPID values from 1 through 50 for internal use, while SPID values 51 or higher represent user sessions.
Permissions
Execute permissions default to the public role.
Examples
A. List all current processes
This example uses sp_who without parameters to report all current users.
USE master
EXEC sp_who
Here is the result set:
spid ecid status loginame hostname blk dbname cmd
---- ---- ------ ------------ -------- --- ------ -----
1 0 background sa 0 pubs LAZY WRITER
2 0 sleeping sa 0 pubs LOG WRITER
3 0 background sa 0 master SIGNAL HANDLER
4 0 background sa 0 pubs RA MANAGER
5 0 background sa 0 master TASK MANAGER
6 0 sleeping sa 0 pubs CHECKPOINT SLEEP
7 0 background sa 0 master TASK MANAGER
8 0 background sa 0 master TASK MANAGER
9 0 background sa 0 master TASK MANAGER
10 0 background sa 0 master TASK MANAGER
11 0 background sa 0 master TASK MANAGER
51 0 runnable DOMAIN\loginX serverX 0 Nwind BACKUP DATABASE
51 2 runnable DOMAIN\loginX serverX 0 Nwind BACKUP DATABASE
51 1 runnable DOMAIN\loginX serverX 0 Nwind BACKUP DATABASE
52 0 sleeping DOMAIN\loginX serverX 0 master AWAITING COMMAND
53 0 runnable DOMAIN\loginX serverX 0 pubs SELECT
(16 row(s) affected)
B. List a specific user's process
This example shows how to view information about a single current user by login name.
USE master
EXEC sp_who 'janetl'
C. Display all active processes
USE master
EXEC sp_who 'active'
D. Display a specific process with process ID
USE master
EXEC sp_who '10' --specifies the process_id