How many users logged on to my database

• robpse

  SSC Enthusiast

  Points: 181

  More actions

  January 5, 2002 at 12:13 pm

  #158028

  What is the easiest way to tell how many users are currently logged on to a database?

  TIA

  Rob

• K. Brian Kelley

  SSC Guru

  Points: 114703

  More actions

  January 5, 2002 at 12:54 pm

  #421768

  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

  bk@warpdrivedesign.org

  http://www.sqlservercentral.com/columnists/bkelley/

  K. Brian Kelley
  @kbriankelley

• Tom Goltl

  SSCertifiable

  Points: 5815

  More actions

  January 7, 2002 at 10:29 am

  #421823

  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