Post reply

Command to know no of users connected to particular database?

  • April 13, 2009 at 3:18 am

    #130557

    Hi,

    Coule you please anybody let me know the command to know the no of users connected to the particular database

    i ran this sp_who2 but it is not extactly giving the results

    Koti

  • April 13, 2009 at 4:13 am

    #976447

    Hmmm how about...

    select count(*) from sys.dm_exec_requests where db_name(database_id) = 'MyDatabaseName'

  • April 13, 2009 at 4:22 am

    #976450

    Hi Koti,

    Can you try this

    select * from sys.sysprocesses where spid >50 and dbid= dbid

  • April 13, 2009 at 5:11 am

    #976459

    Hi,

    The below query will hold good only to know the active users connected to the database ( running state). However if the spid is in sleeping state..that is not captured by the below query.

    select count(*) from sys.dm_exec_requests where db_name(database_id) = 'MyDatabaseName'

    Though the spid is not active, it holds the connection and the resources like memory will be still occupied by that connection.

    So I would prefer querying sys.sysprocesses

    select * from sys.sysprocesses where dbid= 'databaseid'

    As per your requirement you can choose anyone..

    Regards,

    Rajini

  • April 13, 2009 at 6:11 am

    #976488

    Yes, that query returns a more comprehensive result.

    I was trying to find a way to use a DMV rather than querying a system table.

  • April 13, 2009 at 7:58 am

    #976615

    Thank you Rajini

    I will try this and let you know.

    koti

  • April 13, 2009 at 7:59 am

    #976618

    Hi

    i will try this and let you know

    koti

  • April 14, 2009 at 10:19 am

    #977396

    Here is the query you are looking for

    SELECT DB_NAME(dbid) as 'Database Name',

    COUNT(dbid) as 'Total Connections'

    FROM master.dbo.sysprocesses WITH (nolock)

    WHERE dbid > 0

    GROUP BY dbid

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply