sys.sysprocesses (Transact-SQL)

Article
12/03/2008

Contains information about processes that are running on an instance of Microsoft SQL Server. These processes can be client processes or system processes. To access sysprocesses, you must be in the master database context, or you must use the master.dbo.sysprocesses three-part name.

Important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name	Data type	Description
spid	smallint	SQL Server session ID.
kpid	smallint	Microsoft Windows thread ID.
blocked	smallint	ID of the session that is blocking the request. If this column is NULL, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified). -2 = The blocking resource is owned by an orphaned distributed transaction. -3 = The blocking resource is owned by a deferred recovery transaction. -4 = Session ID of the blocking latch owner could not be determined due to internal latch state transitions.
waittype	binary(2)	Reserved.
waittime	bigint	Current wait time in milliseconds. 0 = Process is not waiting.
lastwaittype	nchar(32)	A string indicating the name of the last or current wait type.
waitresource	nchar(32)	Textual representation of a lock resource.
dbid	smallint	ID of the database currently being used by the process.
uid	smallint	ID of the user that executed the command. Overflows or returns NULL if the number of users and roles exceeds 32,767. For more information, see Querying the SQL Server System Catalog.
cpu	int	Cumulative CPU time for the process. The entry is updated for all processes, regardless of whether the SET STATISTICS TIME option is ON or OFF.
physical_io	int	Cumulative disk reads and writes for the process.
memusage	int	Number of pages in the procedure cache that are currently allocated to this process. A negative number indicates that the process is freeing memory allocated by another process.
login_time	datetime	Time at which a client process logged into the server. For system processes, the time at which the SQL Server startup occurred is stored.
last_batch	datetime	Last time a client process executed a remote stored procedure call or an EXECUTE statement. For system processes, the time at which the SQL Server startup occurred is stored.
ecid	smallint	Execution context ID used to uniquely identify the subthreads operating on behalf of a single process.
open_tran	smallint	Number of open transactions for the process.
status	nchar(30)	Process ID status. The possible values are: dormant = SQL Server is resetting the session. running = The session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. For more information, see Using Multiple Active Result Sets (MARS). background = The session is running a background task, such as deadlock detection. rollback = The session has a transaction rollback in process. pending = The session is waiting for a worker thread to become available. runnable = The task in the session is in the runnable queue of a scheduler while waiting to get a time quantum. spinloop = The task in the session is waiting for a spinlock to become free. suspended = The session is waiting for an event, such as I/O, to complete.
sid	binary(86)	Globally unique identifier (GUID) for the user.
hostname	nchar(128)	Name of the workstation.
program_name	nchar(128)	Name of the application program.
hostprocess	nchar(10)	Workstation process ID number.
cmd	nchar(16)	Command currently being executed.
nt_domain	nchar(128)	Microsoft Windows domain for the client, if using Windows Authentication, or a trusted connection.
nt_username	nchar(128)	Windows user name for the process, if using Windows Authentication, or a trusted connection.
net_address	nchar(12)	Assigned unique identifier for the network adapter on the workstation of each user. When a user logs in, this identifier is inserted in the net_address column.
net_library	nchar(12)	Column in which the client's network library is stored. Every client process comes in on a network connection. Network connections have a network library associated with them that enables them to make the connection. For more information, see Network Protocols, and TDS Endpoints.
loginame	nchar(128)	Login name.
context_info	binary(128)	Data stored in a batch by using the SET CONTEXT_INFO statement.
sql_handle	binary(20)	Represents the currently executing batch or object. Note This value is derived from the batch or memory address of the object. This value is not calculated by using the SQL Server 2005 hash-based algorithm.
stmt_start	int	Starting offset of the current SQL statement for the specified sql_handle.
stmt_end	int	Ending offset of the current SQL statement for the specified sql_handle. -1 = Current statement runs to the end of the results returned by the fn_get_sql function for the specified sql_handle.
request_id	int	ID of request. Used to identify requests running in a specific session.

Remarks

If a user has VIEW SERVER STATE permission on the server, the user will see all executing sessions in the instance of SQL Server; otherwise, the user will see only the current session.

Change History

Release

History

14 April 2006

Term

Definition

New content:

Added information to sql_handle about how this value is calculated.

5 December 2005

Term

Definition

Changed content:

Expanded definitions for the blocked column.
Expanded definitions for the status column.

Share via

sys.sysprocesses (Transact-SQL)

Remarks

See Also

Reference

Help and Information

Change History

Additional resources