Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sysprocesses

 

This is a view in the mssqlsystemresource database.

Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more. Use sys.dm_exec_sessions to first view the current system load and to identify a session of interest, and then learn more information about that session by using other dynamic management views or dynamic management functions.

The sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests dynamic management views map to the sys.sysprocesses system table.

Column name Data type Description

session_id

smallint

Identifies the session associated with each active primary connection. Is not nullable.

login_time

datetime

Time when session was established. Is not nullable.

host_name

nvarchar(128)

Name of the client workstation that is specific to a session. The value is NULL for internal sessions. Is nullable.

program_name

nvarchar(128)

Name of client program that initiated the session. The value is NULL for internal sessions. Is nullable.

host_process_id

int

Process ID of the client program that initiated the session. The value is NULL for internal sessions. Is nullable.

client_version

int

TDS protocol version of the interface that is used by the client to connect to the server. The value is NULL for internal sessions. Is nullable.

client_interface_name

nvarchar(32)

Protocol name that is used by the client to connect to the server. The value is NULL for internal sessions. Is nullable.

security_id

varbinary(85)

Microsoft Windows security ID associated with the login. Is not nullable.

login_name

nvarchar(128)

SQL Server login name under which the session is currently executing. For the original login name that created the session, see original_login_name. Can be a SQL Server authenticated login name or a Windows authenticated domain user name. Is not nullable.

nt_domain

nvarchar(128)

Windows domain for the client if the session is using Windows Authentication or a trusted connection. This value is NULL for internal sessions and non-domain users. Is nullable.

nt_user_name

nvarchar(128)

Windows user name for the client if the session is using Windows Authentication or a trusted connection. This value is NULL for internal sessions and non-domain users. Is nullable.

status

nvarchar(30)

Status of the session. Possible values:

  • Running - Currently running one or more requests
     
  • Sleeping - Currently running no requests
     
  • Dormant – Session has been reset because of connection pooling and is now in prelogin state.
     
  • Preconnect - Session is in the Resource Governor classifier.
     

Is not nullable.

context_info

varbinary(128)

CONTEXT_INFO value for the session. The context information is set by the user by using the SET CONTEXT_INFO statement. Is nullable.

cpu_time

int

CPU time, in milliseconds, that was used by this session. Is not nullable.

memory_usage

int

Number of 8-KB pages of memory used by this session. Is not nullable.

total_scheduled_time

int

Total time, in milliseconds, for which the session (requests within) were scheduled for execution. Is not nullable.

total_elapsed_time

int

Time, in milliseconds, since the session was established. Is not nullable.

endpoint_id

int

ID of the Endpoint associated with the session. Is not nullable.

last_request_start_time

datetime

Time at which the last request on the session began. This includes the currently executing request. Is not nullable.

last_request_end_time

datetime

Time of the last completion of a request on the session. Is nullable.

reads

bigint

Number of reads performed, by requests in this session, during this session. Is not nullable.

writes

bigint

Number of writes performed, by requests in this session, during this session. Is not nullable.

logical_reads

bigint

Number of logical reads that have been performed on the session. Is not nullable.

is_user_process

bit

0 if the session is a system session. Otherwise, it is 1. Is not nullable.

text_size

int

TEXTSIZE setting for the session. Is not nullable.

language

nvarchar(128)

LANGUAGE setting for the session. Is nullable.

date_format

nvarchar(3)

DATEFORMAT setting for the session. Is nullable.

date_first

smallint

DATEFIRST setting for the session. Is not nullable.

quoted_identifier

bit

QUOTED_IDENTIFIER setting for the session. Is not nullable.

arithabort

bit

ARITHABORT setting for the session. Is not nullable.

ansi_null_dflt_on

bit

ANSI_NULL_DFLT_ON setting for the session. Is not nullable.

ansi_defaults

bit

ANSI_DEFAULTS setting for the session. Is not nullable.

ansi_warnings

bit

ANSI_WARNINGS setting for the session. Is not nullable.

ansi_padding

bit

ANSI_PADDING setting for the session. Is not nullable.

ansi_nulls

bit

ANSI_NULLS setting for the session. Is not nullable.

concat_null_yields_null

bit

CONCAT_NULL_YIELDS_NULL setting for the session. Is not nullable.

transaction_isolation_level

smallint

Transaction isolation level of the session.
0 = Unspecified
1 = ReadUncomitted
2 = ReadCommitted
3 = Repeatable
4 = Serializable
5 = Snapshot
Is not nullable.

lock_timeout

int

LOCK_TIMEOUT setting for the session. The value is in milliseconds. Is not nullable.

deadlock_priority

int

DEADLOCK_PRIORITY setting for the session. Is not nullable.

row_count

bigint

Number of rows returned on the session up to this point. Is not nullable.

prev_error

int

ID of the last error returned on the session. Is not nullable.

original_security_id

varbinary(85)

Microsoft Windows security ID that is associated with the original_login_name. Is not nullable.

original_login_name

nvarchar(128)

SQL Server login name that the client used to create this session. Can be a SQL Server authenticated login name or a Windows authenticated domain user name. Note that the session could have gone through many implicit or explicit context switches after the initial connection. For example, if EXECUTE AS is used. Is not nullable.

last_successful_logon

datetime

Time of the last successful logon for the original_login_name before the current session started.

last_unsuccessful_logon

datetime

Time of the last unsuccessful logon attempt for the original_login_name before the current session started.

unsuccessful_logons

bigint

Number of unsuccessful logon attempts for the original_login_name between the last_successful_logon and login_time.

group_id

int

ID of the workload group to which this session belongs. Is not nullable.

 




Syntax
CREATE VIEW sys.sysprocesses AS
	SELECT
	spid,
	kpid,
	blocked,
	waittype,
	waittime,
	lastwaittype,
	waitresource,
	dbid,
	uid = convert(smallint, uid),
	cpu,
	physical_io,
	memusage,
	login_time,
	last_batch,
	ecid,
	open_tran,
	status,
	sid,
	hostname,
	program_name,
	hostprocess,
	cmd,
	nt_domain,
	nt_username,
	net_address,
	net_library,
	loginame,
	context_info,
	sql_handle,
	stmt_start,
	stmt_end,
	request_id	
	FROM OpenRowSet(TABLE SYSPROCESSES)

 
Last revision 2008RTM
See also

  sp_addsynctriggerscore (Procedure)
sp_checkusernotloggedin (Procedure)
sp_dbremove (Procedure)
sp_dropdistributiondb (Procedure)
sp_helplogins (Procedure)
sp_makewebtask (Procedure)
sp_MSdbuseraccess (Procedure)
sp_MSdropfkreferencingarticle (Procedure)
sp_MSforeachdb (Procedure)
sp_MSinit_subscription_agent (Procedure)
sp_MSrestoresavedforeignkeys (Procedure)
sp_sysutility_mi_collect_dac_execution_statistics_internal (Procedure)
sp_who (Procedure)
sp_who2 (Procedure)
sysprocesses_ex (View)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash