sp_who

Transact-SQL Reference

Transact-SQL Reference

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

See Also

KILL

sp_lock

sysprocesses

System Stored Procedures