SQL Server 2012远程连接提供超时和异步network_io错误

I am experiencing problems with a service that connects to a remote instance of SQL Server 2012.

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

The .NET 2.0 service runs on the clients machine and it connects using TCP/IP to a remote web server which is currently running an instance of SQL Server 2012. The web server used to be Windows Server 2003 and SQL Server 2005 and was upgraded 6 months ago without any problems. The service then checks to see if there is any data that it needs to download.

. net 2.0服务在客户端机器上运行，它使用TCP/IP连接到当前正在运行SQL server 2012实例的远程web服务器。web服务器曾经是Windows server 2003和SQL server 2005, 6个月前进行了升级，没有出现任何问题。然后，服务会检查是否有需要下载的数据。

Client Machine

客户端机器

Windows Server 2003
Windows Server 2003

Web Server

Web服务器

Windows Server 2012
Windows Server 2012
SQL Server 2012
SQL Server 2012

This was running fine for 5+ years until last Friday when it suddenly stopped working and it gives the following error log message

这个操作可以正常运行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)

I have checked the activity monitor on the web server and when the query is run it is suspended with a WAIT_TYPE of ASYNC_NETWORK_IO.

我已经检查了web服务器上的活动监视器，当查询运行时，它会挂起一个WAIT_TYPE的ASYNC_NETWORK_IO。

I have run the query that it is trying to run manually in Management Studio on the clients server connecting remotely to the web server instance and it runs for about 40 seconds returning 2600+ rows before it gives me the following error

我运行了一个查询，它试图在远程连接到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.)

I have run the same query on my machine in Management Studio remotely connecting to the web server instance and it returns the complete 4000 rows in 0.312 seconds.

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

I am at a loss trying to figure this out as it seems to me to be some sort of network issue. The clients hardware technicians have checked the network and have said that everything is fine as far as they are concerned.

在我看来，这似乎是某种网络问题，我在试图弄明白这一点时不知所措。客户的硬件技术人员已经检查了网络，并说就他们而言一切都很好。

If you need any more information let me know

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

Thanks

谢谢

3 个解决方案

#1

This has been resolved and the issue in the end turned out to be a problem with the clients internet. I dont have all the details but BT found an issue at the exchange and once this was fixed then the program began working again.

这个问题已经解决了，最终问题变成了客户网络的问题。我不知道所有的细节，但英国电信在交易所发现了一个问题，一旦这个问题得到解决，然后这个项目又开始工作了。

#2

Here is something to try. Here is an article on MSDN Blog that suggests 2 things:

这里有一些值得尝试的东西。这是MSDN博客上的一篇文章，它提出了两点建议:

Long running task or uncommited transaction

30 seconds is a default value for your SQL Client to wait. It makes your Client machines to time out. May be the actual data transfer speed for this connection for your clients is too slow to get all rows in time? Maybe another process closes this connection and Client times waiting for a response?

30秒是SQL客户机等待的默认值。它使您的客户端机器超时。可能这个连接的实际数据传输速度对于您的客户端来说太慢以至于不能及时得到所有的行?也许另一个进程会关闭这个连接和等待响应的客户端时间?

#3

Judging from the resolution by others who have had (somewhat) similar problems, this almost always turns out to be one of three things:

从其他有(或多或少)类似问题的人的解决方案来看，这几乎总是三件事中的一件:

Corrupt or failing disk(s). Use CHKDSK /r to address this.

腐败或失败的磁盘(s)。使用CHKDSK /r来解决这个问题。
A bad NIC, or

一个糟糕的网卡,或
DHCP problems, particularly with the Lease Expiration/Renewal.

DHCP问题，特别是租约到期/续期。

I am not aware of any cases where it turned out to be anything specific to SQL Server.

我不知道有哪一种情况是SQL Server特有的。

#1

#2