I saw an article in the forum today asking why the session ID of the SQL server system ranges from 1 to 50. I have read this article before, these sessions are used by SQL Server to run system activities such as (lazy writer, ghost record cleanup, DTC commit/abort). Therefore, 50 session IDs are reserved for SQL Server, user sessions start from 51.
Query user session usage before 2005:
Select *
From sysprocesses
Wherespid <50
However, this restriction is no longer available after SQL Server 2005. I found the following article on msdn: How it works: System sessions
Looking at a SQL Server Error Log it is formatted withDate,
TimeAndSession identifier. Identifier of the identifiers contain
SFollowing the spid value.
2008-01-08 20:03:36. 12 spid5S
TheSIndicates that the session is a system session. prior to SQL Server 2005 all system sessions were limited to session IDs less than 50. SQL Server 2005 lifted that restriction. in order identify a session during Ming system Actives
(Lazy writer, ghost record cleanup, DTC commit/abort,...) The sessions are identified as system sessions.
Instead of the older"Select * From sysprocesses where spid <50"You shoshould use"Select * From SYS. dm_exec_sessions where is_user_process = 0"To identify system processes.
According to the preceding method, the session ID of the system may exceed 50.
Select *
From SYS. dm_exec_sessionswhereis_user_process = 1
Is_user_process |
Bit |
If the session is a system session, it is 0. Otherwise, it is 1. Cannot be null |
For more information, see http://msdn.microsoft.com/zh-cn/library/ms176013 (V = SQL .90). aspx