2

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. !?

  • What kinds of waits are you seeing in sys.dm_os_wait_stats? I've got a hunch that it's THREADPOOL, and could be fixed by increasing Maximum worker threads. – StrayCatDBA Apr 8 '13 at 19:34
  • What are the general specs on the target server (OS, CPU, RAM, disk, SQL Edition)? Is there only one client connecting to the server? – James Jan 31 '15 at 2:03
  • Also, it's a long shot, but have you checked 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. – James Jan 31 '15 at 2:10
0

You've like got a developer that's not closing the connections and causing connection pool growth. Periodically recycle the connection pool or try using a sql trace to identify when new connections are created so you can identify and fix the problem code.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.