SQL server - get list of active connections to each database

Posts: 33
Comments: 186
Trackbacks: 0

<< Find out last modification to the SQL table | Home | SQL Server - get last executed SQL statement >>

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.

Share This Post:
Short Url: http://wblo.gs/dcg

posted on Monday, March 4, 2013 3:24 PM Print

Comments

# 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.

# 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"

# 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

# 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.

# re: SQL server - get list of active connections to each database: Jennifer Keller; 11/6/2016 8:57 PM; Very new to this!! What is "dbid"? Is this where I put in the name of my database? Or do I replace "DB_NAME" with the name of my database? Or, is there no need for the name of mine to be in here?

# re: SQL server - get list of active connections to each database: Pierce09; 5/16/2018 8:56 AM; Here is good list of the important components of SQL server. People who are learning SQL server must understand and use of these components while creating connection to make a app, software or a website. Well! I want rushmyessay but this is reliable site to learn the basic points about programming.

Post Comment

Title *
Name *
Email
Comment *
Verification: Remember Me?

Archives

Post Categories

Vipin Agarwal

Post Comment