How It Works: System Sessions
Published Jan 15 2019 10:48 AM 236 Views
First published on MSDN on Jan 10, 2008

The Senior Escalation Engineers do various training and mentoring activities.  As I do this I thought I would try to propagate some of this information on the blog.

Looking at a SQL Server error log it is formatted with the date , time and session identifier .   Many of the identifiers contain the s following the spid value.

2008-01-08 20:03:36.12 spid5 s

The s indicates 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 performing 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 should use " select * from sys.dm_exec_sessions where is_user_process = 0 " to identify system processes.

Bob Dorr
SQL Server Senior Escalation Engineer

Version history
Last update:
‎Jan 15 2019 10:48 AM
Updated by: