SQL Server“与网络有关或与实例有关的错误"；每天一次(困惑！) [英] SQL Server "network-related or instance-specific error" once a day or so (perplexed!)

问题描述

我们遇到的错误与

We are experiencing the same error as this StackOverflow Q ...

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)

...除了在引用的StackOverflow Q中，一旦发生错误，他们需要重新启动SQL Server，而我们没有.我们每天或每隔几天会收到一次此错误-发生错误后，直到下一次发生，一切都很好.

... except that in the referenced StackOverflow Q, they need to restart SQL Server once the error occurs - and we do not. We'll get this error once a day, or once every few days - and all is fine after the error occurs, until the next time it occurs.

这使我们认为这不是忘记关闭连接"问题.我们有一个繁忙的ASP.NET 4.0 WebForms/SQL Server 2008 R2应用程序；但是我们很肯定我们没有超过数据库连接的最大数量.

This makes us think it's not a "forgot to close connections" issue. We have a moderately busy ASP.NET 4.0 WebForms / SQL Server 2008 R2 app; but we're quite positive we're not exceeding the max # of database connections.

对这个问题有任何想法或诊断方法吗?

Any thoughts on this problem, or an approach to diagnose?

推荐答案

我想对我们在此方面的进展进行评论.

Thought I would comment on our progress with this.

尽管没有任何SQL Server文档/文章/博客提到此错误可能是由服务器繁忙引起的，但我发现

While none of the SQL Server documentation/articles/blogs mention that this error can be caused by server busyness, I found a forum posting where some seasoned IT pro named Matt Neerincx states that it can be, as follows:

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 we are presently walking down this path - reducing the # of queries that execute at the same time in some of our batch queries, optimizing some of our queries, etc.

此外，在我们的应用程序连接字符串中，我们增加了连接超时，并将最小池大小"设置为20(认为最好尝试确保一些现有的，未使用的连接供应用程序获取，而不是需要建立新的连接连接).

Also, in our app connection string, we increased the connection timeout, and set Min Pool Size to 20 (thinking it's good to try to ensure some existing, unused connections for the app to grab, rather than needing to establish a new connection).

目前，已经快48小时没有收到错误了；使我们充满希望.

At this moment, it's been almost 48 hours without receiving the error; making us very hopeful.

这篇关于SQL Server“与网络有关或与实例有关的错误"；每天一次(困惑！)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！