January 26, 2005 at 8:56 am
Is there a method to determine the total number of active connections to a database?
The reason I ask is that a vendor we have quite simply can't tell us if their program needs access to a SQL box (and then their program sends the information it's received out to our workstations) or if each seperate workstation has it's own connection. I've heard it being both ways, that we need a CAL for each workstation, and that we'd need less than 5 CAL's to work with their software. (The joys of working through sales people instead of with the developers themselves)
Thanks in advance for any help.
-Terry
January 26, 2005 at 10:05 am
Try this, if you don't want by s specific DB, jsut rip that part out.
SET NOCOUNT ON
DECLARE @DBName AS VarChar(50)
SET @DBName = '?????'
SELECT
'Distinct Users - ' + CAST(COUNT(Distinct(loginame)) AS CHAR(10))
FROM
master.dbo.sysprocesses
WHERE
(
dbid = DB_ID(@DBName)
)
SELECT
'Total Connections - ' + CAST(COUNT(loginame) AS CHAR(10))
FROM
master.dbo.sysprocesses
WHERE
(
dbid = DB_ID(@DBName)
)
SELECT
CAST(status AS CHAR(12)) AS Status,
CAST(rtrim(loginame) AS CHAR(18)) AS 'LoginName',
CAST(hostname AS CHAR(18)) AS HostName,
CAST(db_name(dbid) AS CHAR(20)) AS 'DbName'
FROM
master.dbo.sysprocesses
WHERE
(
dbid = DB_ID(@DBName)
)
ORDER BY
LoginName,
DBName
KlK
January 26, 2005 at 10:12 am
sp_who2 will also give you all connections and you can check that for the program name, or workstation name, or login name.
Profiler can be setup to scan for the users and record logins/logouts. You could then analyze this information for concurrent connection counts over time.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 27, 2005 at 9:37 am
You might want to look into the sysprocesses table ... there are a few columns present (if the application connection string populates them) that might make this a snap.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
January 30, 2005 at 2:44 pm
select db, usr, count(*) as processes from
( select sp.loginame as usr, sd.name as db
from sysprocesses sp join sysdatabases sd on sp.dbid = sd.dbid ) as db_usage
where db like('%')
group by db, usr
order by db, usr
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply