Count Database Connections, List Database Connections

Useful script to count the number of connections by user. Also lists the connections to the database. We used this to find a problem where the database performance was very slow (frozen you could say). It turned out to be caused by the application not releasing the connection once it had finished.

Hope that helps, Jon Bosker, DB Gurus, 18 Nov 2015


DECLARE @DatabaseName varchar(MAX)
SET @DatabaseName = 'MyDatabase' --<< put your database name in here

-- Count the number of connection by login
SELECT loginame, COUNT(*)
FROM master..sysprocesses
WHERE dbid=db_id(@DatabaseName)
GROUP BY loginame

-- List the connections
SELECT *
FROM master..sysprocesses
WHERE dbid=db_id(@DatabaseName)