January 5, 2002 at 12:13 pm
What is the easiest way to tell how many users are currently logged on to a database?
TIA
Rob
January 5, 2002 at 12:54 pm
One of the easiest way is to use Performance Monitor and the SQL Server: General Stats >> User Connections counter.
There is also a way to get a value equivalent to the counter using T-SQL. This one hits the system table sysprocesses, but it does return the number of connections. I filter on program_name to eliminate processes that are SQL Server's:
SELECT COUNT(*) Connections FROM sysprocesses
WHERE REPLACE(program_name, ' ', '') <> ''
I didn't filter out SQL Agent because the counter doesn't seem to. When I started SQL Agent I saw the user connections jump by two in Performance Monitor.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 7, 2002 at 10:29 am
Here is a stored procedure that I have. It tells me the current connections in a database. You can either give it the name of the database you want to check or it will take the current database that you are in.
Tom Goltl
CREATE procedure sp_users @vcDbnm varchar(30)
as
declare @vcdbname varchar(100)
declare @dbid smallint
/* Get the dbid from sysdatabases */
if (@vcdbnm = '')
begin
select @dbid = dbid from master.dbo.sysprocesses
where spid = @@spid
select name from master.dbo.sysdatabases
where dbid = @dbid
print 'Database'
print upper(@vcDbName)
print ''
print ''
select a.*
from master.dbo.sysprocesses a
where
a.dbid = @dbid
end
else
begin
select @vcdbName = @vcdbnm
select upper(@vcDbName)
print ''
print ''
select a.*
from master.dbo.sysprocesses a
where
a.dbid = (Select dbid from master.dbo.sysdatabases
where name = @vcDbName)
end
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply