SQL SERVER – How to See Active SQL Server Connections For Database

Another question received via email –

“How do I I know which user is connected to my database with how many connection?”

Here is the script which will give us answer to the question.

SELECT DB_NAME(dbid) AS DBName,
COUNT(dbid) AS NumberOfConnections,
loginame
FROM    sys.sysprocesses
GROUP BY dbid, loginame
ORDER BY DB_NAME(dbid)

Here is the resultset:

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL DMV
Previous Post
Developer’s Life – Summary of Superhero Articles
Next Post
SQL Authority News – Secret Tool Box of Successful Bloggers: 52 Tips to Build a High Traffic Top Ranking Blog

Related Posts

8 Comments. Leave new

  • Santosh S Pawar
    November 20, 2015 2:56 pm

    Hi sir in above result No of connection to master DB is 15 why its showing 15, if i open one query tab then instead of showing 1 connection extra its showing 3 connections why it is showing like this , how it work..?

    Reply
  • Goran Yordanov
    October 26, 2016 7:46 pm

    A slightly modified :

    SELECT
    DB_NAME(dbid) AS DBName,
    COUNT(dbid) AS NumberOfConnections,
    loginame
    FROM
    sys.sysprocesses
    GROUP BY
    DB_NAME(dbid),
    loginame
    ORDER BY
    DB_NAME(dbid)

    Reply
  • Fantastic Thank you!

    Reply
  • Sir, I need you help.
    I want a query which will tell me how many users have connected to a DB from Solution explorer. All these queries which used either the DMV’s or sys.process helps us get the information about number of active aconnections(from sql query window). But I need to know login name and number of connections from solution explorer.

    I am building a Audit specification without using the inbuilt audit features since we faced a downtime using that service. Please can you help. ITS URGENT.

    Appreciate your SQL articles. I just love them :)

    Thanks,
    Chinmayee

    Reply
    • What is “Solution explorer”. Is that the application name? Does it come in any column in SQL? If yes, use that as a filter in the DMV.

      Reply
  • Majid Sayyed
    May 17, 2019 2:42 pm

    More simple way. Simply run :
    SHOW FULL PROCESSLIST
    as a query in phpmyadmin and it will give you the complete list of connections with users and other details.
    Like we run a query “SELECT * FROM …”. So simply run “SHOW FULL PROCESSLIST”

    Reply
  • Hello,
    what does mean by no of connections are 15 & 27 loginname wtih sa? and same with other db also? why it is showing?

    Reply
  • Hi …Code works great.
    How can I do the same thing, but find the active connections to a specific table in a specific database?
    Thing is, there are several databases on our instance of SQL Server. And in each database, there are different groups (in the company) that share that Database. So, I’m only interested in certain tables, within a certain Schema for that matter. Can this be done?

    Reply

Leave a Reply

Menu
Exit mobile version