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 8 (of 8 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy