February 2, 2015 at 8:34 am
I am researching this and playing with using the syss.sysprocesses view and counting the dbid column. I would most likely want to store this information in a table with a date/time stamp because we really want to track our concurrent users during the day for awhile. Do you have any neat tricks in getting this done? Is there a DMV or Information_Schema view that could be used to accomplish this task? I found the following code on the internet:
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
GROUP BY dbid,
hostname,
loginame,
nt_domain,
nt_username
ORDER BY NumberOfConnections DESC;
Thanks in advance, Kevin
February 2, 2015 at 8:44 am
sys.sysprocesses is legacy, and there are a handful of DMVs that could do the same job better.
The following will simply return number of connections.
select * from sys.dm_os_performance_counters
where Counter_Name = 'User Connections';
The following is similar to sys.sysprocesses, but gives more detail. For example, it will return client IP address, protocol, authentication, last read, and a sql_handle which can join back to text of last executed sql statement.
select * from sys.dm_exec_connections;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply