Posts
27
Comments
68
Trackbacks
0
SQL server - get list of active connections to each database
Many times, we want list of all users who are connected to the SQL server - to determine it  use the below query. It returns the Database, Number of open connections and logged-in user credentials.

SELECT DB_NAME(dbid) as Database, COUNT(dbid) as Number Of Open Connections,
loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame

Hope this helps.
posted on Monday, March 4, 2013 3:24 PM Print
Comments
Gravatar
# re: SQL server - get list of active connections to each database
Jack Grahl
5/16/2014 11:08 AM
Thanks a lot for this.

At least in my version of MSSQL, Database is a keyword. You can't use it as a column name unless you put it in quotes.
Gravatar
# re: SQL server - get list of active connections to each database
Hafiz
12/8/2014 9:21 PM
that shit doesn't work.
Gravatar
# re: SQL server - get list of active connections to each database
Hafiz
12/8/2014 9:23 PM
that shit doesn't work.
Gravatar
# re: SQL server - get list of active connections to each database
Hafiz
12/8/2014 9:23 PM
that shit doesn't work.
Gravatar
# re: SQL server - get list of active connections to each database
John C
12/15/2014 6:00 PM
Thanks. Very useful. I had to put brackets or double quotes around the field name aliases to get them to work properly.

Examples:
SELECT DB_NAME(dbid) as [Database]
or
SELECT DB_NAME(dbid) as "Database"
Gravatar
# re: SQL server - get list of active connections to each database
johnD
12/30/2014 7:24 PM
SELECT DB_NAME(dbid) as "Database", COUNT(dbid) as "Number Of Open Connections",

loginame as LoginName

FROM sys.sysprocesses

WHERE dbid > 0

GROUP BY dbid, loginame
Gravatar
# re: SQL server - get list of active connections to each database
andy
2/18/2015 10:28 AM
Is there a way to determine if they are valid? I have found that a machine that loses connectivity (unplugged cable in test) leaves a connection for hours.

Post Comment

Title *
Name *
Email
Comment *  
Verification