In troubleshooting why I was seeing so many connections to one of our internally used databases in SQL Server 2012.
A query I found online that had been helpful to me was this:
SELECT DB_NAME(sP.dbid) AS the_database
, COUNT(sP.spid) AS total_database_connections
FROM sys.sysprocesses sP
GROUP BY DB_NAME(sP.dbid)
ORDER BY 1;
However, although after fixing an issue, I've seen connections to our own custom database go back to normal levels, I'm always seeing the number of connections to the master
database always at over 30 connections.
Is this normal for SQL Server 2012? Anyone else getting 30 or more connections to master
when running this query?
Best Answer
30 system processes is absolutely not unheard of, and actually sounds better than normal.
However, you should not be using
sysprocesses
, which is deprecated and only exists for backward compatibility reasons - see the important note right at the top of the doc. (If you're using it solely for the database column, see this recent question, and the comments.)You should be looking at
sys.dm_exec_sessions
- this view has a column calledis_user_process
that allows you to identify your processes and distinguish them from system processes (the old < 50 trick is no longer reliable on modern versions of SQL Server).Just as an example, on my local dev machine - where I can assure you nothing is going on - I have the following rows:
The majority of those 26 user processes are just connections to the instance (which is on a VM guest) held open by Visual Studio Code on the host.
The other 42 are normal, sleeping, background system processes like
LOG WRITER
,LOCK MONITOR
,XE DISPATCHER
, 'CHECKPOINT', etc. It's not like you can get rid of these.You could also compare the output of the undocumented
sys.sp_who2
:Or limiting to actual running processes in
sys.dm_exec_requests
.But for deeper insight into what processes are doing on your system, you're better off using something like Adam Machanic's sp_whoisactive or a proper monitoring tool (disclaimer: I work for one of those).