Total number of active/open SQL Server connections, per database

If you've managed SQL Server, I'm sure you've experienced problems with client connections.  There are countless client libraries for SQL Server, and sometimes there can be just as many problems with each of them.  :-(

 

The database connection is done in several steps;  Establish connection, the handshake, parse the connection string, and then SQL has to authenticate.  Then, checks are done at the database and the transaction level, and more.

 

Unfortunately, when clients disconnect, the connection is not always closed properly.  PHP, for example, is known for problems releasing the connection, which creates 'orphaned' connections.  Typically caused by problems at the app, or even network-related problems, but whatever it is, these orphans can very easily consume valuable resources from your server!

 

In this post I've just given you a method to return the active/open connections, per database. Both sp_who or sp_who2 are great, but they output a LOT of detail that you need to scroll through, and translate.  This is just a fast way to get in there, and count your connections, per database.

 

   /* Return Active/Open connections per database. */ 
   SELECT
      DB_NAME(dbid) [Database],
      hostname [Host],
      d.client_net_address [IPaddress],
      loginame [Login],
      COUNT(dbid) [NoOfConnections]
   FROM
      sys.sysprocesses s INNER JOIN sys.dm_exec_connections d
        ON s.spid = d.session_id
   WHERE
      d.session_id >= 51 -- << user sessions are >= 51
   GROUP BY
      s.dbid,
      s.hostname, 
      d.client_net_address,
      s.loginame


These are my results.  If I was on a larger network, we'd see other IPs and host machines:

	Database	Host	IPaddress	Login	NoOfConnections
	master	MYPCNAME	<local machine>	MYPCNAME\MyName	6

Please see this for more detail on sys.dm_exec_connections:
      http://msdn.microsoft.com/en-us/library/ms181509.aspx

Also see this for more detail on sys.sysprocesses.  But, don't miss the backward compatibility note!  This one has been replaced by sys.dm_exec_connections, sys.dm_exec_sessions and sys.dm_exec_requests, and it will be gone in a future release:
     http://technet.microsoft.com/en-us/library/ms179881.aspx