Recently, our developers have increased the Pool size in all connection strings to 8000. They are using VS 2010 Framework 4.0. In the DB side I am monitoring that up to 4000 connections is opening. But the problem is we have tones of error logs (see below) hand-shake time-out problems.
For me it is a very wired problem and I could not find any clue. I know with the server that I have (real monster) we are able to support more connections opened. I know Max is 32767, but I never expect to reach this number, but four kilo is far less.
Connection Strings :
connectionString 1 ="server=S1;database=DB1;Integrated Security=false; user id=user; Password=password; Min Pool Size=10; Max Pool Size=8000; Connect Timeout=5;" providerName="System.Data.SqlClient"/>
.
.
.
connectionString 10 ="server=S1;database=DB10;Integrated Security=false; user id=user; Password=password; Min Pool Size=10; Max Pool Size=8000; Connect Timeout=5;" providerName="System.Data.SqlClient"/>
And the error Log :
GXMT.Common.DataGateway.DAOException: Executed command: System.Data.SqlClient.SqlException: Connection Timeout Expired. The timeout period elapsed during the post-login phase. The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=1; handshake=3565; [Login] initialization=0; authentication=0; [Post-Login] complete=1001; ---> System.ComponentModel.Win32Exception: The wait operation timed out
Thanks.
PS : New discovery is when the number of connection exceed 4000, even in Sql Server Management Studio opening a connection takes around 25 seconds. !?
select * from sys.configurations where name = 'user connections'
? This is the server-side limit, which appears to be set to 32767 by default, but could have been changed.