Geek Answers Handbook

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

We have a very strange problem with an intermittent situation that began to appear in the last month or so, when some connections to the mssql server fail with an 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 crash the site and does not require restarting db - if you just restart the same request, it will work a second time. This means that many users will occasionally encounter an error and need to refresh the error page to work.

Now my initial reaction to the knee jerk was due to:

  • The problem with resources is why I started running the SQL profiler and perfmon, but I did not find any problems with the service trying to keep up with the number of connections / sec. I watched MSSQL: SQL errors, MSSQL: wait statistics, MSSQL: Exec statistics, MSSQL: locks. Does anyone have any recommendations on other statistics that I have to push and push here?

  • Unblocked database connections - I selected this code after passing through the entire data layer code. We have all the fail-safe safes to stop this.

  • Connection / network problem: our SQL server is located on a separate server (MS SQL Server Standard 2008) on our application server (ASP.Net runs on IIS7) - both servers work on xlarge Amazon EC2 instances with all security policies configured (in accordance with the direction of the Amazons). Has anyone received guidance on testing the connection between two servers, or if this could be a problem?

  • Is this a possible problem with the IIS connection string? I have not tested this, but should we fully qualify the server with the name of the computer with which we are connecting (just thinking about it)? We use the connection string in the format: server=xxxxx;Database=xxxx;uid=xxxx;password=xxx;

Your thoughts and understanding are greatly appreciated!

Thank you in advance

+4
source share
4 answers

solved. After testing almost all possible performance indicators and examining every part of the code, I found that the error was caused by a bit outdated database code. The main problem was caused by code using:

 SqlConnection.ClearPools;

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

+3
source

Try changing the connection string to FQDN + port

 server=xxxxx.domain.tld,1234;

Note: you do not need instance name if you use port

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

After some conversation, talking with database administrators and MS, he was said to be caused by the synchronization of / Kerberos / with too many firewalls, etc. Adding the FQDN + port removed all our problems.

+3
source

It seems that the connection is closed incorrectly, and after some time you cannot open new connections. Because the total valid database connection is a constant number.

If you are using C # / VB.net

Do you use Usage instructions to open connections?

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

This can be resolved by switching to TCP / IP instead of Named Pipes, if possible. Perhaps you can verify this by changing the server name to the server IP address.

I use server = tcp: server_name in my connection string for forced TCP. KB313295

0
source

More articles:


All Articles