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 4, 2013 10:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:56 PM
Points: 394, Visits: 1,976
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 4, 2013 1:18 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:54 PM
Points: 4,319, Visits: 3,362
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
String Splitting with True Performance
Best practices on how to ask questions
Post #1511271
Posted Tuesday, November 5, 2013 2:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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 5, 2013 5:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:56 PM
Points: 394, Visits: 1,976
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 6, 2013 5:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:56 PM
Points: 394, Visits: 1,976
How to resolve this issue?
Post #1511829
Posted Thursday, November 7, 2013 2:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,362, Visits: 15,269
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 7, 2013 2:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,362, Visits: 15,269
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 7, 2013 7:35 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:56 PM
Points: 394, Visits: 1,976
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 7, 2013 7:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,362, Visits: 15,269
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 7, 2013 7:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,362, Visits: 15,269
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