Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

user connections sql server Expand / Collapse
Author
Message
Posted Monday, November 04, 2013 10:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:28 PM
Points: 351, Visits: 1,657
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?
Post #1511189
Posted Monday, November 04, 2013 1:18 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 10:37 PM
Points: 3,306, Visits: 2,351
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



Tally Tables - Performance Personified
Best practices on how to ask questions
Post #1511271
Posted Tuesday, November 05, 2013 2:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 4:45 AM
Points: 2,385, Visits: 1,843
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."
Post #1511398
Posted Tuesday, November 05, 2013 5:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:28 PM
Points: 351, Visits: 1,657
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


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.
Post #1511442
Posted Wednesday, November 06, 2013 5:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:28 PM
Points: 351, Visits: 1,657
How to resolve this issue?
Post #1511829
Posted Thursday, November 07, 2013 2:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:08 AM
Points: 1,350, Visits: 15,200
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.
Post #1512157
Posted Thursday, November 07, 2013 2:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:08 AM
Points: 1,350, Visits: 15,200
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
Post #1512158
Posted Thursday, November 07, 2013 7:35 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:28 PM
Points: 351, Visits: 1,657
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.

Post #1512265
Posted Thursday, November 07, 2013 7:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:08 AM
Points: 1,350, Visits: 15,200
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.
Post #1512273
Posted Thursday, November 07, 2013 7:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:08 AM
Points: 1,350, Visits: 15,200
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
Post #1512275
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse