SQL Server : Query to Get Information about Database Connections

Sometime DBA need to look at the connections made on various databases on Server or even the process that are going on the SQL Server. Below queries will give you detail of the connections.

This query will will return you database wise and login wise total no of connections on the database server.

GO

SELECT DB_NAME(dbid) AS DatabaseName, loginame AS LoginName, COUNT(dbid) AS TotalConnections
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame

GO

sp_who2 system stored procedure provides information about the concurrent users and connection, with "Active" parameter it excludes sessions that are waiting for the next command from the user.

sp_who2 'Active'

GO

Below query will will return total of of the current connection on the Database Server.

SELECT COUNT(dbid) as TotalConnections FROM sys.sysprocesses WHERE dbid > 0