user connections sql server

  • How many user connections can sql server handle? In the sp_configure the default values for min 0 and max 32767. Can we increase these number of connections and configure it to a higher value ? So after reaching the max value does the sql server stop accepting new connections?

  • muthyala_51 (11/4/2013)


    How many user connections can sql server handle? In the sp_configure the default values for min 0 and max 32767. Can we increase these number of connections and configure it to a higher value ? So after reaching the max value does the sql server stop accepting new connections?

    The default value of 0 means unlimited. See http://technet.microsoft.com/en-us/library/ms187030%28v=sql.100%29.aspx

  • The actual number of user connections allowed also depends on the version of SQL Server that you are using, and also the limits of your application or applications and hardware. SQL Server allows a maximum of 32,767 user connections. Because user connections is a dynamic (self-configuring) option, SQL Server adjusts the maximum number of user connections automatically as needed, up to the maximum value allowable. For example, if only 10 users are logged in, 10 user connection objects are allocated. In most cases, you do not have to change the value for this option. The default is 0, which means that unlimited user connections are allowed.

    Ref: http://technet.microsoft.com/en-us/library/ms187030.aspx

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • free_mascot (11/5/2013)


    The actual number of user connections allowed also depends on the version of SQL Server that you are using, and also the limits of your application or applications and hardware. SQL Server allows a maximum of 32,767 user connections. Because user connections is a dynamic (self-configuring) option, SQL Server adjusts the maximum number of user connections automatically as needed, up to the maximum value allowable. For example, if only 10 users are logged in, 10 user connection objects are allocated. In most cases, you do not have to change the value for this option. The default is 0, which means that unlimited user connections are allowed.

    Ref: http://technet.microsoft.com/en-us/library/ms187030.aspx%5B/quote%5D

    On one of our prod server after we reach around 32k connections. The server is not allowing new connections. While we connect thru ssms its throwing error a severe error occurred while connecting to the server. Jobs are failing and some jobs are missing their schedule. No info in the event viewer. After thorough investigation we came to the conclusion that it's not allowing newconnections after reaching tht threshold value.

  • How to resolve this issue?

  • muthyala_51 (11/6/2013)


    How to resolve this issue?

    This should never happen because of connection pooling and points to 1 or many poorly written applications maintaining open connections. Unless you really have more than 32,000 applications running concurrently then you need to look there.

  • muthyala_51 (11/6/2013)


    How to resolve this issue?

    BTW this is a physical limitation of SQL. There is no "work around" other than to fix the problem

  • Jimbo,

    thank you. But in the above responses somebody said the default is 0 and that means unlimited. Which one is true ?

    We started modifying our scripts from applications.

  • http://technet.microsoft.com/en-us/library/ms143432.aspx

    User connections | 32,767

    http://technet.microsoft.com/en-us/library/ms187030.aspx

    SQL Server allows a maximum of 32,767 user connections. Because user connections is a dynamic (self-configuring) option, SQL Server adjusts the maximum number of user connections automatically as needed, up to the maximum value allowable. For example, if only 10 users are logged in, 10 user connection objects are allocated. In most cases, you do not have to change the value for this option. The default is 0, which means that unlimited user connections are allowed.

    I assume, 0 means unlimited connections up to the maximum value allowable.

  • I was looking for some useful information regarding connection pooling and found this excellent blog post to describe it on msdn.

    http://blogs.msdn.com/b/sql_pfe_blog/archive/2013/10/08/connection-pooling-for-the-sql-server-dba.aspx

  • Thanks a lot !! will go through them.

Viewing 11 posts - 1 through 10 (of 10 total)

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