SQL

Wednesday 20 March 2013

SQL Server Connection Timeout

I had a problem recently on play-free-games.com whereby I was hitting connection timeouts while connecting to SQL Server 2008 from my .Net application using ADO.

I would stress the point that these were connection timeouts rather than command timeouts. In many cases the timeout occurred when calling SqlConnection.Open(), without even defining a command to execute.

What surprised me (I'm easily surprised) was that the timeouts were almost instantaneous - within 1-2 seconds. SQL Server connection strings include a parameter called "Connection Timeout", which I had not set, but the default is listed as 15 seconds.

I wasted a lot of time asking network nerds to trace the connection, check for dodgy switches etc, only to find a Microsoft blog post stating that in some circumstances the timeout applied will only be 8% of what is specified if your SQL server is mirrored:

http://msdnrss.thecoderblogs.com/2011/05/ado-net-application-connecting-to-a-mirrored-sql-server-database-may-timeout-long-before-the-actual-connection-timeout-elapses-sometimes-within-milliseconds/

With this in mind I increased the timeout value in my connection string to 200, and the problem disappeared except at peak usage times.

I then added a "Min Pool Size" setting of 30, and the problem disappeared altogether.

Top hole!