CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

How It Works: System Sessions

How It Works: System Sessions

Rate This
  • Comments 2

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 spid5s

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

Leave a Comment
  • Please add 7 and 7 and type the answer here:
  • Post
Page 1 of 1 (2 items)