Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Command to know no of users connected to particular database? Expand / Collapse
Author
Message
Posted Monday, April 13, 2009 3:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 14, 2010 6:06 AM
Points: 76, Visits: 199

Hi,

Coule you please anybody let me know the command to know the no of users connected to the particular database

i ran this sp_who2 but it is not extactly giving the results


Koti
Post #695607
Posted Monday, April 13, 2009 4:13 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, October 26, 2014 2:30 PM
Points: 495, Visits: 868
Hmmm how about...


select count(*) from sys.dm_exec_requests where db_name(database_id) = 'MyDatabaseName'
Post #695627
Posted Monday, April 13, 2009 4:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 16, 2014 1:06 AM
Points: 206, Visits: 328
Hi Koti,

Can you try this

select * from sys.sysprocesses where spid >50 and dbid= dbid
Post #695630
Posted Monday, April 13, 2009 5:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 6, 2010 1:37 PM
Points: 259, Visits: 436
Hi,

The below query will hold good only to know the active users connected to the database ( running state). However if the spid is in sleeping state..that is not captured by the below query.

select count(*) from sys.dm_exec_requests where db_name(database_id) = 'MyDatabaseName'

Though the spid is not active, it holds the connection and the resources like memory will be still occupied by that connection.

So I would prefer querying sys.sysprocesses
select * from sys.sysprocesses where dbid= 'databaseid'

As per your requirement you can choose anyone..

Regards,
Rajini
Post #695641
Posted Monday, April 13, 2009 6:11 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, October 26, 2014 2:30 PM
Points: 495, Visits: 868
Yes, that query returns a more comprehensive result.
I was trying to find a way to use a DMV rather than querying a system table.
Post #695676
Posted Monday, April 13, 2009 7:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 14, 2010 6:06 AM
Points: 76, Visits: 199
Thank you Rajini

I will try this and let you know.

koti
Post #695813
Posted Monday, April 13, 2009 7:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 14, 2010 6:06 AM
Points: 76, Visits: 199
Hi

i will try this and let you know

koti
Post #695816
Posted Tuesday, April 14, 2009 10:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 8:48 PM
Points: 2, Visits: 46
Here is the query you are looking for


SELECT DB_NAME(dbid) as 'Database Name',
COUNT(dbid) as 'Total Connections'
FROM master.dbo.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid
Post #696744
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse