Query to find number of users connected in an MS SQL database

 

Sometimes you may get an error from your application saying "Connection pool size exceeded".

Here I am going to explain you few queries which can be used to find out the number of open connections in your MS SQL database and also the query to find the maximum connections allowed in SQL server.

First we will check, what is the maximum connections SQL server will allow you to connect.

SELECT @@MAX_CONNECTIONS AS 'Maximum Connections Allowed in the database'

Now you know that how many connections you can have in your database. So we will check how many are already used.

Below query can be used to find the number of connections in each database in your server if you execute this query against master database.

SELECT DB_NAME(dbid) as [Database Name], COUNT(dbid) as [Number of Connections] from master.dbo.sysprocesses with (nolock)

WHERE dbid > 0 GROUP BY dbid

Now we will see who all are the users connected to your database.

Below query will show you the login name and the host name of the user connected to your database. In below query you may change the database name when you execute in your system.

SELECT LOGINAME=RTRIM(LOGINAME),HOSTNAME FROM MASTER.DBO.SYSPROCESSES

WHERE DB_NAME(DBID) = 'DataBaseName' AND DBID != 0

Now at the end we will see a very simple query which can be used to show all the sessions that are currently established in the database.

sp_who2