Post reply

How to determine concurrent user ?

  • November 6, 2006 at 8:49 pm

    #170366

    How to know there is how many users currently login to the database ?

  • November 6, 2006 at 11:58 pm

    #670228

    How many users, or how many connections?

    For number of connections,

    SELECT

    count(*) FROM sys.dm_exec_connections

    Some users may have more than one connection

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 7, 2006 at 1:38 am

    #670237

    Let say I have a system that restricted maximum 5 users login concurrently. So I want to check the number of user currently using the system. I would like to prompt an error message if the 6th user trying to login. Is there any solution for the problem ?

    Thanks.

  • November 7, 2006 at 2:01 am

    #670238

    Hi,

    I think you can used the stored procedure SP_WHO, SP_WHO2 to identified how many users.

    I hope this will slove your problem

    cheers

  • November 7, 2006 at 3:11 am

    #670244

    Again, 5 users (5 different user names) or 5 connections? Is a single user allowed to make multiple connections to the database, and if so, do you count each of them, or ust 1 since it's the same user?

    If you're counting connections then something like this will work

    IF

    (SELECT count(*) FROM sys.dm_exec_sessions WHERE Session_id>50) > 5

    BEGIN

    RAISERROR ('Error. too many concurrent connections, please try again later',16,1)

    RETURN

    END

    If you're counting users (login names), then try this

    IF

    (SELECT count(distinct login_name) FROM sys.dm_exec_sessions WHERE Session_id>50) > 5

    BEGIN

    RAISERROR ('Error. too many concurrent connections, please try again later',16,1)

    RETURN

    END

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 7, 2006 at 6:13 pm

    #670444

    Thanks so much. I've solved my problem.

  • November 8, 2006 at 12:05 am

    #670479

    Is sys.dm_exec_sessions  something SQL Server 2005 specific? On SQL Server 2000 it doesn't seem to work and  I always used master.dbo.sysprocesses to get at this sort of information.

  • November 8, 2006 at 12:26 am

    #670481

    Yes, it's one of the new SQL 2005 DM views. Since this is the SQL 2005 forum, I did the 2005 solution.

    You can do the same thing with sysprocesses in 2000, you'll just have to change the column names.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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