C# 你如何处理 SqlConnection 中的传输级错误？

How do you deal with transport-level errors in SqlConnection?

提问by Eric Z Beard

Every now and then in a high volume .NET application, you might see this exception when you try to execute a query:

在大容量 .NET 应用程序中，当您尝试执行查询时，您可能会时不时地看到以下异常：

System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server.

System.Data.SqlClient.SqlException: 向服务器发送请求时发生传输级错误。

According to my research, this is something that "just happens" and not much can be done to prevent it. It does not happen as a result of a bad query, and generally cannot be duplicated. It just crops up maybe once every few days in a busy OLTP system when the TCP connection to the database goes bad for some reason.

根据我的研究，这是“刚刚发生”的事情，并且无法采取任何措施来阻止它。它不会因错误查询而发生，并且通常无法复制。在繁忙的 OLTP 系统中，当与数据库的 TCP 连接由于某种原因出现故障时，它可能每隔几天就会出现一次。

I am forced to detect this error by parsing the exception message, and then retrying the entire operation from scratch, to include using a new connection. None of that is pretty.

我被迫通过解析异常消息来检测此错误，然后从头开始重试整个操作，包括使用新连接。没有一个是漂亮的。

Anybody have any alternate solutions?

有人有任何替代解决方案吗？

回答by Kevin Goff

You should also check hardware connectivity to the database.

您还应该检查与数据库的硬件连接。

Perhaps this thread will be helpful: http://channel9.msdn.com/forums/TechOff/234271-Conenction-forcibly-closed-SQL-2005/

也许这个线程会有所帮助：http: //channel9.msdn.com/forums/TechOff/234271-Conenction-forcably-closed-SQL-2005/

回答by Rinat Abdullin

I'm using reliability layer around my DB commands (abstracted away in the repository interfaece). Basically that's just code that intercepts any expected exception (DbException and also InvalidOperationException, that happens to get thrown on connectivity issues), logs it, captures statistics and retries everything again.

我在我的数据库命令周围使用可靠性层（在存储库界面中抽象出来）。基本上，这只是拦截任何预期异常（DbException 和 InvalidOperationException，碰巧在连接问题上抛出）、记录它、捕获统计信息并再次重试所有内容的代码。

With that reliability layer present, the service has been able to survive stress-testing gracefully (constant dead-locks, network failures etc). Production is far less hostile than that.

有了这个可靠性层，服务就能够正常地经受住压力测试（持续的死锁、网络故障等）。生产远没有那么敌对。

PS: There is more on that here(along with a simple way to define reliability with the interception DSL)

PS：这里有更多内容（以及使用拦截 DSL 定义可靠性的简单方法）

回答by Daniel Fortunov

To answer your original question:

要回答您的原始问题：

A more elegant way to detect this particular error, without parsing the error message, is to inspect the Numberproperty of the SqlException.

检测到这种特定错误的更优雅的方式，不必解析错误信息，是检查Number的财产SqlException。

(This actually returns the error number from the first SqlErrorin the Errorscollection, but in your case the transport error should be the only one in the collection.)

（这实际上返回从第一个错误号SqlError中Errors集，但在你的情况下，传输错误应该是唯一一个集合中）。

回答by Jesper Blad Jensen

I had the same problem. I asked my network geek friends, and all said what people have replied here: Its the connection between the computer and the database server. In my case it was my Internet Service Provider, or there router that was the problem. After a Router update, the problem went away. But do you have any other drop-outs of internet connection from you're computer or server? I had...

我有同样的问题。我问了我的网络极客朋友，都说这里有人回答：是计算机和数据库服务器之间的连接。就我而言，是我的 Internet 服务提供商或路由器出了问题。路由器更新后，问题消失了。但是，您的计算机或服务器是否有任何其他互联网连接中断？我有...

回答by Tim Farley

I posted an answer on another questionon another topic that might have some use here. That answer involved SMB connections, not SQL. However it was identical in that it involved a low-level transport error.

我在另一个主题的另一个问题上发布了一个答案，这里可能有一些用处。该答案涉及 SMB 连接，而不是 SQL。然而，它的相同之处在于它涉及一个低级别的传输错误。

What we found was that in a heavy load situation, it was fairly easy for the remote server to time out connections at the TCP layersimply because the server was busy. Part of the reason was the defaults for how many times TCP will retransmit data on Windows weren't appropriate for our situation.

我们发现，在负载较重的情况下，远程服务器很容易因为服务器繁忙而使 TCP 层的连接超时。部分原因是 TCP 在 Windows 上重新传输数据的默认次数不适合我们的情况。

Take a look at the registry settings for tuning TCP/IPon Windows. In particular you want to look at TcpMaxDataRetransmissionsand maybe TcpMaxConnectRetransmissions. These default to 5 and 2 respectively, try upping them a little bit on the client system and duplicate the load situation.

查看用于在 Windows 上调整 TCP/IP的注册表设置。特别是您想查看TcpMaxDataRetransmissions和TcpMaxConnectRetransmissions。这些分别默认为 5 和 2，尝试在客户端系统上稍微提高它们并复制负载情况。

Don't go crazy! TCP doubles the timeout with each successive retransmission, so the timeout behavior for bad connections can go exponential on you if you increase these too much. As I recall upping TcpMaxDataRetransmissionsto 6 or 7 solved our problem in the vast majority of cases.

不要发疯！TCP 每次连续重传都会使超时加倍，因此如果您将这些增加太多，则不良连接的超时行为可能会呈指数级增长。我记得在绝大多数情况下将TcpMaxDataRetransmissions 提高到 6 或 7 解决了我们的问题。

回答by Dale Wright

I have seen this happen in my own environment a number of times. The client application in this case is installed on many machines. Some of those machines happen to be laptops people were leaving the application open disconnecting it and then plugging it back in and attempting to use it. This will then cause the error you have mentioned.

我在自己的环境中多次看到这种情况发生。这种情况下的客户端应用程序安装在许多机器上。其中一些机器碰巧是笔记本电脑，人们将应用程序打开断开连接，然后重新插入并尝试使用它。这将导致您提到的错误。

My first point would be to look at the network and ensure that servers aren't on DHCP and renewing IP Addresses causing this error. If that isn't the case then you have to start trawlling through your event logs looking for other network related.

我的第一点是查看网络并确保服务器不在 DHCP 上并更新导致此错误的 IP 地址。如果情况并非如此，那么您必须开始浏览事件日志以寻找其他与网络相关的内容。

Unfortunately it is as stated above a network error. The main thing you can do is just monitor the connections using a tool like netmon and work back from there.

不幸的是，如上所述，这是网络错误。您可以做的主要事情就是使用像 netmon 这样的工具监视连接，然后从那里开始工作。

Good Luck.

祝你好运。

回答by Magnus Lindhe

This blog postby Michael Aspengrenexplains the error message "A transport-level error has occurred when sending the request to the server."

该博客文章由迈克尔Aspengren解释错误信息“发送请求到服务器时发生传输级错误。”

回答by Martin

I had the same problem albeit it was with service requests to a SQL DB.

我遇到了同样的问题，尽管是对 SQL DB 的服务请求。

This is what I had in my service error log:

这是我的服务错误日志中的内容：

---

---

System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

System.Data.SqlClient.SqlException: 向服务器发送请求时发生传输级错误。（提供者：TCP 提供者，错误：0 - 远程主机强行关闭了现有连接。）

---

---

I have a C# test suite that tests a service. The service and DB were both on external servers so I thought that might be the issue. So I deployed the service and DB locally to no avail. The issue continued. The test suite isn't even a hard pressing performance test at all, so I had no idea what was happening. The same test was failing each time, but when I disabled that test, another one would fail continuously.

我有一个测试服务的 C# 测试套件。服务和数据库都在外部服务器上，所以我认为这可能是问题所在。所以我在本地部署了服务和数据库无济于事。问题还在继续。测试套件甚至根本不是一个严格的性能测试，所以我不知道发生了什么。同样的测试每次都失败，但是当我禁用该测试时，另一个测试会不断失败。

I tried other methods suggested on the Internet that didn't work either:

我尝试了互联网上建议的其他方法也不起作用：

• Increase the registry values of TcpMaxDataRetransmissionsand TcpMaxConnectRetransmissions.
• Disable the "Shared Memory" option within SQL Server Configuration Manager under "Client Protocols" and sort TCP/IP to 1st in the list.
• This might occur when you are testing scalability with a large number of client connection attempts. To resolve this issue, use the regedit.exe utility to add a new DWORD value named SynAttackProtect to the registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\ with value data of 00000000.

• 增加TcpMaxDataRetransmissions和TcpMaxConnectRetransmissions的注册表值。
• 禁用 SQL Server 配置管理器中“客户端协议”下的“共享内存”选项，并将 TCP/IP 排序为列表中的第一个。
• 当您通过大量客户端连接尝试测试可伸缩性时，可能会发生这种情况。要解决此问题，请使用 regedit.exe 实用程序将名为 SynAttackProtect 的新 DWORD 值添加到注册表项 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\，其值数据为 00000000。

My last resort was to use the old age saying "Try and try again". So I have nested try-catch statements to ensure that if the TCP/IP connection is lost in the lower communications protocol that it does't just give up there but tries again. This is now working for me, however it's not a very elegant solution.

我最后的手段是使用老话“再试一次”。所以我嵌套了 try-catch 语句，以确保如果 TCP/IP 连接在较低的通信协议中丢失，它不仅会放弃，还会再次尝试。这现在对我有用，但这不是一个非常优雅的解决方案。

回答by Jon Black

use Enterprise Services with transactional components

将企业服务与事务组件一起使用

回答by Phillip Deneka

I experienced the transport error this morning in SSMS while connected to SQL 2008 R2 Express.

我今天早上在连接到 SQL 2008 R2 Express 时在 SSMS 中遇到了传输错误。

I was trying to import a CSV with \r\n. I coded my row terminator for 0x0d0x0a. When I changed it to 0x0a, the error stopped. I can change it back and forth and watch it happen/not happen.

我试图用 \r\n 导入 CSV。我将行终止符编码为 0x0d0x0a。当我将其更改为 0x0a 时，错误停止了。我可以来回改变它，看着它发生/不发生。

 BULK INSERT #t1 FROM 'C:3\Import123.csv' WITH 
      ( FIRSTROW = 1, FIELDTERMINATOR = ',', ROWTERMINATOR = '0x0d0x0a' )

I suspect I am not writing my row terminator correctly because SQL parses one character at a time right while I'm trying to pass two characters.

我怀疑我没有正确编写行终止符，因为在我尝试传递两个字符时，SQL 一次解析一个字符。

Anyhow, this error is 4 years old now, but it may provide a bit of information for the next user.

不管怎样，这个错误现在已经有 4 年了，但它可能会为下一个用户提供一些信息。