Query to get Max no of concurrent users connected

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Query to get Max no of concurrent users connected

Author

Topic

vijay1234
Starting Member

48 Posts

Posted - 2014-07-31 : 11:25:45

Hi All,

I would require to know a query that will fetch the max concurrent users connected to the databases in a particular server for a particular month.

Ex:
We have DB's called A,B,C,D ( Excluding sys DB's) in a server called X.

So the max list of users connected to the DB - A,B,C,D for a particular month say 'July'

Result should be like

DB Max No of Users Month
A 10 July
B 8 July
C 45 July
D 76 July

Highly appreciate the response friends.

Thanks & Regards,
Vijay

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-31 : 12:26:21

Are you storing sysprocesses or similar? If not, then you'll need to start taking a snapshot of the info so that you can later query it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

vijay1234
Starting Member

48 Posts

Posted - 2014-07-31 : 22:45:43

Hi Tara,

Yes. We've got sysprocesses.

Any way that i can get a query ?

I'm using this

SELECT DB_NAME(dbid) AS DBName,
COUNT(dbid) AS NumberOfConnections,
loginame AS LoginName,
nt_domain AS NT_Domain,
nt_username AS NT_UserName,
hostname AS HostName
FROM sys.sysprocesses
WHERE dbid > 0 and DB_NAME(dbid) not in ('master','msdb','model','tempdb')
GROUP BY dbid,
hostname,
loginame,
nt_domain,
nt_username
ORDER BY NumberOfConnections DESC;

But looking for the desired result set as mentioned in my prior one

Appreciate your revert on this Tara

Thanks
Vijay

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-01 : 12:22:51

But are you storing that info? sysprocesses is for the current processes. If you need to go back a month, you have to start storing the data from sysprocesses.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources