SQL Server "network-related or specific instance error" once a day or so (puzzled!)

Question

We are experiencing the exact same error as https://stackoverflow.com/a/318929/

System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at System.Data.Linq.SqlClient.SqlConnectionManager.UseConnection(IConnectionUser user) at System.Data.Linq.SqlClient.SqlProvider.get_IsSqlCe() at System.Data.Linq.SqlClient.SqlProvider.InitializeProviderMode() at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) 

... except that in the reference StackOverflow Q they must restart SQL Server after an error occurs - and we do not. We will receive this error once a day or once every few days - and everything will be fine after the error occurs, until the next time.

This makes us think that this is not a “forgot about close ties” question. We have a moderately loaded ASP.NET 4.0 WebForms / SQL Server 2008 R2 application; but we are quite sure that we do not exceed the maximum number of connections to the database.

Any thoughts on this issue or diagnostic approach?

Answer 1

I think I would comment on our progress with this.

Although none of the SQL Server documentation / articles / blogs mentions that this error could be caused by server busyness , I found a forum where some experienced IT professionals named Matt Neerincx state that this could be the following:

 Possible reasons for this error include: 1. Poor network link from client to server. 2. Server is very busy (meaning high CPU) and cannot respond to new connection attempts. 3. Server is running out of memory (so high memory usage for SQL). 4. tcp-ip layer on client is over-saturated with connection attempts so tcp-ip layer rejects the connection. 5. tcp-ip layer on server side is over-staturated with connection attempts and so tcp-ip layer is rejecting new connections. 6. With SQL 2005 SP2 and later there could be a custom login trigger that rejects your connection. You can increase the connect timeout to potentially alleviate issues #2, #3, #4, #5. Setting a longer connect timeout means the driver will try longer to connect and may eventually succeed. To determine the root cause of these intermittent failures is not super easy to do unfortunately. What I normally do is start by examining the server environment, is the server constantly running in high CPU for example, this points to #2. Is the server using a hugh amount of memory, this points to #3. You can run SQL Profiler to monitor logins and look for patterns of logins, perhaps every morning at 9AM there is a flurry of connections etc... 

So, now we are going this way - reducing the number of requests that are executed simultaneously in some of our batch requests, optimizing some of our requests, etc.

In addition, in our application connection string, we increased the connection timeout and set the Min Pool Size to 20 (assuming that it is good to try to provide some existing, unused connections for the application to capture, instead of establishing a new connection )

At this point, it was almost 48 hours without receiving an error; which makes us very encouraging.