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
. Many of the identifiers contain the
following the spid value.
2008-01-08 20:03:36.12 spid5
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.