Intermittent SQL Exception - network-related or instance-specific error

We have a very strange intermittent issue which has started coming up over the last month or so whereby some connections to mssql server fail with the error:

System.Data.SqlClient.SqlException: 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

The error does not bring down the site, nor does it require a db restart - if you simply rerun the same query will work the second time. This means a lot of users will hit an error every now and then and have 开发者_如何学Goto refresh the error page for things to work.

Now, my initial knee-jerk reaction was this could be due to:

1. Resource related issue - so I started running SQL profiler and perfmon, but did not find any issues with the serve struggling to keep up with the number of connections / sec. I've been looking at MSSQL:SQL Errors, MSSQL:Wait Statistics, MSSQL:Exec Statistics, MSSQL:Locks. Does anyone have any guidance on other stats I should be poking and prodding here?

2. Unclosed DB connections - I ruled this one out after going through all the data-tier code. We have all the fail safes in place to stop this from happening.

3. Connection / Network related issue: our SQL server sits on a separate server (MS SQL Server Standard 2008) to our application server (running ASP.Net on IIS7) - both servers run on xlarge Amazon EC2 instances with all security policies configured (as per Amazons direction). Anyone got guidance on how to test the connectivity between the two servers or if this could be the issue?

4. Is it a possible issue with the IIS connection string? I have not tested this but should we be fully qualifying the server with the computer name we are connecting to (just thought of it)? We use a connection string in the format: server=xxxxx;Database=xxxx;uid=xxxx;password=xxx;

Your thoughts and insight is very much appreciated!

Thanks in advance

Solved. After testing almost every possible performance metric and examining every piece of code, I discovered that the error was caused by a bit of deprecated database code. The main issue was being caused by code using:

SqlConnection.ClearPools;

For future reference, any other developers looking to debug their code and manage connection pools, an excellent resource can be found here: http://www.codeproject.com/KB/dotnet/ADONET_ConnectionPooling.aspx

Try changing the connection string to the FQDN+port

server=xxxxx.domain.tld,1234;

Note: you don't need any instance name if you use port

On our global corporate intranet... we had a similar issue that happened to remote clients: more often if they were further away, never in the same building as the server.

After some poking around, chatting to the DBAs and MS, it was said to be caused by timing/Kerberos/too many firewalls etc. Adding FQDN+port removed all our issues.

It may be solved by switching to TCP/IP instead of Named Pipes, if you can. Perhaps you can test this by changing the server name to the server IP address.

I use server=tcp:servername in my connection string to force TCP. KB313295

It seems like connection are not being closed correctly, and after some time you can't open any more new connections. As the total allowed connections to database is a constant digit.

If you are using C#/VB.net

Are you using "Using" statements to open the connections ?

using (System.Data.SqlClient.SqlConnection con = new SqlConnection("YourConnection string")) 
{ 
    con.Open(); 
}