c# - .net + SQL + 超时问题

Question

我在连接到 SQL Server 实例的 .net 库方面遇到问题。

该库是将 edi 文件的内容插入 SQL Server 2008 R2 数据库的应用程序的一部分。

每次调用该库时，它都会抛出一个异常(下面的#1)，说明连接已超时。有问题的服务器上有多个其他非 .net 应用程序使用相同的连接字符串并且工作正常。

我还在另一台联网机器上安装了包含该库的应用程序，它在那里运行良好。

我尝试过的事情:

Setting .CommandTimeout = 150; - Times out way before 150 secs.
Reinstalling all .net frameworks
Rebooting app server & DB server
Switching from data context to standard data adapter queries.
Created a stand alone exe to test connection string using both data context and standard data adapter queries - Both methods fail first time, but then connect and read data on all subsequent tries until the exe is restarted. This lead me to add a retry attempt with a delay to the library when the exception is hit, but that results in exception 2.

我现在很迷茫。任何帮助将不胜感激。

异常(exception)情况 1:

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
   at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable)
   at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean withFailover)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject, Boolean withFailover)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
   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)
   at System.Data.Linq.DataContext.ExecuteMethodCall(Object instance, MethodInfo methodInfo, Object[] parameters)
   at System.Data.Linq.DataContext.ExecuteQuery[TResult](String query, Object[] parameters)

<Query Execution method call>

异常(exception) 2:

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   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)
   at System.Data.Linq.DataContext.ExecuteMethodCall(Object instance, MethodInfo methodInfo, Object[] parameters)
   at System.Data.Linq.DataContext.ExecuteQuery[TResult](String query, Object[] parameters)

<Query Execution method call>

Answer 1

Setting .CommandTimeout

发生在查询无法在指定时间内获取总数据时..

在您的情况下，可能会发生多次超时。我们按照以下步骤解决了我们案例中的超时方法

1.将 RemoteLoginTimeout 更改为 60
2.将 RemoteQuery 超时更改为 0..

此外，我们还在所有服务器上禁用了 TCP Chimney

https://blogs.msdn.microsoft.com/psssql/2010/02/21/tcp-offloading-again/

现在我们 100% 确定，网络不是超时的一个因素，即使查询因超时而失败，当通过 SQLagent 或其他东西执行时也是如此

1.我们尝试更新统计数据
2.我们将看看是否有优化的机会..

也可以引用this blog ，其中 OP 提到了所有可能发生的超时

When attempting to get a connection from the SqlClient connection pool
When attempting to create a new fresh connection to server (not getting one from pool)
When sending a command explicitly to the server
When sending commands with "context connection=true"
When sending commands implicitly (under the covers) to the server
When executing asynchronous commands (BeginExecute…)
When waiting for attention acknowledgement from server (special rare case)
When sending TM commands
When fetching rows
When uploading rows using bulk copy