.net - SQL Server 2012 远程连接给出超时和 ASYNC_NETWORK_IO 错误

Question

我在连接到 SQL Server 2012 远程实例的服务时遇到问题。

.NET 2.0 服务在客户端计算机上运行，​​它使用 TCP/IP 连接到当前运行 SQL Server 2012 实例的远程 Web 服务器。Web 服务器曾经是 Windows Server 2003 和 SQL Server 2005，并且6 个月前升级，没有任何问题。该服务然后检查是否有任何数据需要下载。

客户端机器

• window 服务器 2003

网络服务器

• window 服务器 2012
• SQL Server 2012

这已经运行了 5 年多，直到上周五突然停止工作并给出以下错误日志消息

Event Type: Warning
Event Source:   
Event Category: None
Event ID:   0
Date:       21/05/2013
Time:       16:45:11
User:       N/A
Computer:   
Description:
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> 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.TdsParserStateObject.ReadByteArray(Byte[] buff, Int32 offset, Int32 len)
   at System.Data.SqlClient.TdsParserStateObject.ReadUInt32()
   at System.Data.SqlClient.TdsParser.ReadSqlValueInternal(SqlBuffer value, Byte tdsType, Int32 typeId, Int32 length, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.ReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ReadColumnData()
   at System.Data.SqlClient.SqlDataReader.ReadColumnHeader(Int32 i)
   at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)
   at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
   at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
   at RedBlack.Data.Synchronisation.SyncManager.UpsertRecords(String sourceSelect, String destTable, String[] destFields, List`1 relations)
   at General.CyPro.Application.WebSynchronisation.SyncRoutines.FetchOrders(UpsertFlags upsertOptions)
   --- End of inner exception stack trace ---
   at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
   at System.Delegate.DynamicInvokeImpl(Object[] args)
   at RedBlack.CyPro.Application.WebSynchronisation.Job.Run(Object[] args)
...
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.TdsParserStateObject.ReadByteArray(Byte[] buff, Int32 offset, Int32 len)
   at System.Data.SqlClient.TdsParserStateObject.ReadUInt32()
   at System.Data.SqlClient.TdsParser.ReadSqlValueInternal(SqlBuffer value, Byte tdsType, Int32 typeId, Int32 length, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.ReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ReadColumnData()
   at System.Data.SqlClient.SqlDataReader.ReadColumnHeader(Int32 i)
   at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)
   at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
   at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
   at RedBlack.Data.Synchronisation.SyncManager.UpsertRecords(String sourceSelect, String destTable, String[] destFields, List`1 relations)
   at General.CyPro.Application.WebSynchronisation.SyncRoutines.FetchOrders(UpsertFlags upsertOptions)

我检查了 Web 服务器上的事件监视器，当查询运行时，它被挂起，WAIT_TYPE 为 ASYNC_NETWORK_IO。

我已经运行了它试图在远程连接到 Web 服务器实例的客户端服务器上的 Management Studio 中手动运行的查询，它运行了大约 40 秒返回 2600 多行，然后出现以下错误

Msg 121, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

我在远程连接到 Web 服务器实例的 Management Studio 中的计算机上运行了相同的查询，它在 0.312 秒内返回了完整的 4000 行。

我在尝试解决这个问题时不知所措，因为在我看来这是某种网络问题。客户的硬件技术人员检查了网络，并表示就他们而言一切正常。

如果您需要更多信息，请告诉我

谢谢

Answer 1

从其他有(有点)类似问题的人的解决方案来看，这几乎总是证明是以下三种情况之一:

1. 损坏或故障磁盘。使用 CHKDSK/r 解决这个问题。

2. 网卡坏了，或者

3. DHCP 问题，尤其是租约到期/续订。

我不知道有任何情况表明它是特定于 SQL Server 的。