September 15, 2014 at 6:33 am
Hi - Iv'e been asked to find out the number of connecting users and devices for some of our SQL2008 Servers. This is required as part of a license audit, does anyone know where I'm able to obtain this information?
Any help would be very much appreciated.
Regards
Dax
September 15, 2014 at 6:46 am
exec sp_who2 might be what you need.
September 15, 2014 at 7:17 am
You can see live connections by querying sys.dm_exec_requests. But to really audit connections, I'd suggest using the extended events. You can then capture all connection events to the server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 15, 2014 at 7:27 am
Hi, maybe this can help:
SELECTdb_name(dbid) AS DB,
count(dbid) AS Connections,
loginame AS LoginName
FROMsys.sysprocesses
GROUP BY dbid, loginame
ORDER BY DB
September 15, 2014 at 7:57 am
Hi - Many thanks for the reply. Does this give me the number of user connections per DB or device connections?
Kind Regards
Dax
September 15, 2014 at 8:41 am
dax.latchford (9/15/2014)
Hi - Many thanks for the reply. Does this give me the number of user connections per DB or device connections?Kind Regards
Dax
Any of the methods listed can. But if you want literal counts of connections over time, you're best off with the extended events. The other queries are going to require additional work to get the counts because you'll have to eliminate duplicates and poll them over and over. It really depends on what exactly you want.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply