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

3
  • 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. Apr 8, 2013 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, 2015 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, 2015 at 2:10

1 Answer 1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .