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)
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
9 Comments. Leave new
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..?
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)
Fantastic Thank you!
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
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.
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”
Hello,
what does mean by no of connections are 15 & 27 loginname wtih sa? and same with other db also? why it is showing?
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?
Hi All,
I am looking for Inactive DB users of all of the databases on SQL instance