How It Works: System Sessions

Published Jan 15 2019 10:48 AM 137 Views
Microsoft
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


%3CLINGO-SUB%20id%3D%22lingo-sub-315432%22%20slang%3D%22en-US%22%3EHow%20It%20Works%3A%20System%20Sessions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-315432%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Jan%2010%2C%202008%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3E%3CEM%3E%20The%20Senior%20Escalation%20Engineers%20do%20various%20training%20and%20mentoring%20activities.%26nbsp%3B%20As%20I%20do%20this%20I%20thought%20I%20would%20try%20to%20propagate%20some%20of%20this%20information%20on%20the%20blog.%20%3C%2FEM%3E%3C%2FP%3E%0A%20%20%3CP%3ELooking%20at%20a%20SQL%20Server%20error%20log%20it%20is%20formatted%20with%20the%20%3CEM%3E%20date%20%3C%2FEM%3E%20%2C%20%3CEM%3E%20time%20%3C%2FEM%3E%20and%20%3CEM%3E%20session%20%3C%2FEM%3E%20%3CEM%3E%20identifier%20%3C%2FEM%3E%20.%26nbsp%3B%26nbsp%3B%20Many%20of%20the%20identifiers%20contain%20the%20%3CSTRONG%3E%20s%20%3C%2FSTRONG%3E%20following%20the%20spid%20value.%3C%2FP%3E%0A%20%20%3CBLOCKQUOTE%3E%3CBR%20%2F%3E%3CP%3E2008-01-08%2020%3A03%3A36.12%20spid5%20%3CSTRONG%3E%20s%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%0A%20%20%3CP%3EThe%20%3CSTRONG%3E%20s%20%3C%2FSTRONG%3E%20indicates%20that%20the%20session%20is%20a%20system%20session.%26nbsp%3B%26nbsp%3B%20Prior%20to%20SQL%20Server%202005%20all%20system%20sessions%20were%20limited%20to%20session%20ids%20less%20than%2050.%26nbsp%3B%20SQL%20Server%202005%20lifted%20that%20restriction.%26nbsp%3B%26nbsp%3B%20In%20order%20identify%20a%20session%20performing%20system%20actives%20(lazy%20writer%2C%20ghost%20record%20cleanup%2C%20DTC%20commit%2Fabort%2C%20...)%20the%20sessions%20are%20identified%20as%20system%20sessions.%3C%2FP%3E%0A%20%20%3CP%3EInstead%20of%20the%20older%20%3CEM%3E%20%22select%20*%20from%20sysprocesses%20where%20spid%20%26lt%3B%2050%22%20%3C%2FEM%3E%20you%20should%20use%20%22%20%3CEM%3E%20select%20*%20from%20sys.dm_exec_sessions%20where%20is_user_process%20%3D%200%20%3C%2FEM%3E%20%22%20to%20identify%20system%20processes.%3C%2FP%3E%0A%20%20%3CP%3EBob%20Dorr%20%3CBR%20%2F%3E%20SQL%20Server%20Senior%20Escalation%20Engineer%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-315432%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jan%2010%2C%202008%20The%20Senior%20Escalation%20Engineers%20do%20various%20training%20and%20mentoring%20activities.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-315432%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQL%20OS%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Etroubleshooting%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 15 2019 10:48 AM
Updated by: