November 4, 2013 at 10:01 am
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?
November 4, 2013 at 1:18 pm
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
November 5, 2013 at 2:10 am
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."
November 5, 2013 at 5:22 am
free_mascot (11/5/2013)
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.
November 6, 2013 at 5:37 am
How to resolve this issue?
November 7, 2013 at 2:33 am
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.
November 7, 2013 at 2:34 am
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
November 7, 2013 at 7:35 am
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.
November 7, 2013 at 7:42 am
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.
November 7, 2013 at 7:46 am
I was looking for some useful information regarding connection pooling and found this excellent blog post to describe it on msdn.
November 7, 2013 at 7:50 am
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