如何配置SQL Server连接以避免大文件处理时的超时与强制关闭错误？

我编写了如下代码处理CSV文件数据入库：

var liveConnection = new SqlConnection(AppConfig.dbMainConnectionString);
liveConnection.Open();

try
{
    var itemGroup = getItems(); // 该方法读取FTP服务器上上传的新.CSV文件并返回itemgroup列表

    foreach(var item in itemGroup)
    {
        // 使用liveConnection执行带item参数的存储过程
    } 

    liveConnection.Close();
}
catch(...) { ... }
finally(...) { ... }

处理小文件时运行正常，但处理约20万条记录的大CSV文件时，日志出现以下错误：

• 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.)
• Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
• Database error: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

从场景来看，数据库服务器在文件处理过程中主动关闭了连接，导致报错。我想知道是否有特定设置可以延长连接时长，避免数据库服务器关闭该连接？

我了解ConnectionTimeout和CommandTimeout，但两者都不适用：ConnectionTimeout仅在打开连接时生效，CommandTimeout针对单个命令执行，而循环里的每个更新/插入命令都是简单操作，默认30秒足够。另外我们没有用连接池，因为应用每天仅运行3次左右，只在处理文件时需要连接数据库。

1. 调整SQL Server端的连接相关设置 #

SQL Server有几个参数会直接影响连接的存活时长：

• 远程查询超时：默认值为600秒（10分钟），如果你的数据处理总时长超过这个时间，服务器会主动断开连接。可以通过以下SQL语句修改（需管理员权限）：

  sp_configure 'remote query timeout', 0; -- 设置为0表示无超时限制
  RECONFIGURE;
  

  注意：无超时设置可能带来潜在风险，需结合业务实际情况评估后使用。
• TCP保持活动设置：如果连接长时间处于空闲状态（比如读取CSV文件时没有数据库操作），服务器TCP层可能会断开连接。可以在SQL Server的TCP/IP配置中调整保持活动时间，或者在连接字符串中添加KeepAlive=30（单位：秒），让客户端定期发送心跳包维持连接。

2. 优化数据库连接字符串 #

在你的dbMainConnectionString中添加以下参数，增强连接稳定性：

• KeepAlive=30：每30秒发送一次TCP心跳包，防止防火墙或服务器因连接空闲而断开。
• Connection Lifetime=0：若后续启用连接池，该参数控制连接在池中的最长存活时间；当前未用连接池，添加后也不会产生负面影响。
• 若无需加密传输，可添加Encrypt=False：加密会增加额外开销，大流量场景下可能加剧连接不稳定问题。

3. 代码层面优化（更高效的解决方案） #

单条循环处理20万条记录的效率极低，是连接长时间被占用的根源，优化代码比单纯调设置更有效：

• 批量操作：改用SqlBulkCopy直接批量导入CSV数据，或者使用表值参数（TVP）将多条记录打包后调用存储过程，大幅减少数据库交互次数，缩短连接占用时间。
• 分段处理：将20万条记录拆分为多个批次（比如每1万条一批），处理完一批后关闭当前连接，重新打开新连接处理下一批，避免单个连接长时间占用。
• 调整连接时机：先调用getItems()读取完所有CSV数据（或分段读取），再打开数据库连接进行批量写入，避免连接在读取CSV的空闲阶段被服务器断开。

4. 排查网络层面限制 #

防火墙、路由器等网络设备通常会设置空闲连接超时（比如默认30分钟），若数据处理时长超过这个阈值，网络设备会主动断开连接。需要联系运维人员调整相关设备的超时设置，确保连接在处理过程中不会被中断。

内容的提问来源于stack exchange，提问作者asten_ark