sql server 存储过程异常，发生传输级错误 TCP ERROR

Question

最近遇到一个很奇怪的问题：
sql server 定时job调用SSIS，SSIS再调用执行存储过程，有出现如下异常.
很奇怪的是并非一直报错，发生频率是偶尔、有时会出错.
存储过程加上try catch异常捕获机制 或者 @@ERROR全局变量中也抓不到错误
sql server 日志中也没有异常记录，就感觉突然中止了
异常的存储过程中并不连接其他数据库，仅本地表进行数据转换计算.

只剩job的执行的异常信息如下：(有时也报 执行的网络名不可用或信号灯超时时间已到)

消息
已以用户 NT Service\SQLSERVERAGENT 的身份执行。 Microsoft (R) SQL Server 执行包实用工具 Version 11.0.7001.0 for 64-bit 版权所有(C) Microsoft Corporation。保留所有权利。 开始时间: 0:42:19 由于错误 0x80131904，无法执行 IS 服务器包。服务器: ************，包路径: \SSISDB\Projection\Projection\PPM_Daily_Process_2.dtsx，环境引用 ID: NULL。 说明: 在从服务器接收结果时发生传输级错误。 (provider: TCP Provider, error: 0 - 信号灯超时时间已到) 源: .Net SqlClient Data Provider 开始时间: 0:42:19 完成时间: 0:59:36 占用时间: 1036.84 秒. 包执行失败。. 该步骤失败。

为了追踪异常，我在存储过程逐行打了日志：发现每次都是停在存储过程里的一行SQL,如下：

SELECT DISTINCT C.MAIN_ROUTE_ID, C.OP_NO, C.SEQ, C.DIFF_SEQ, CONVERT(INT,NULL) AS N_DIFF_SEQ
, CONVERT(INT,NULL) AS BLOCK_NAME
INTO #T_CT
FROM PPM.M_CYT_STD_CT C
WHERE (C.DIFF_SEQ IS NULL

    OR ISNULL(C.DIFF_SEQ,999999) <> (SELECT TOP 1 ISNULL(DIFF_SEQ,999999) FROM PPM.M_CYT_STD_CT
                WHERE MAIN_ROUTE_ID = C.MAIN_ROUTE_ID
                    AND SEQ = C.SEQ-1))
ORDER BY 1, 3

将SQL 拆开：透过执行日志发现每次停在CYT_STEP_075 和 CYT_STEP_076之间，问题应该在这句SQL上.
但是手动执行也是没错的，问题时偶然性的.

-- 2021/7/6 分次写入临时表
SELECT DISTINCT C.MAIN_ROUTE_ID, C.OP_NO, C.SEQ, C.DIFF_SEQ, CONVERT(INT,NULL) AS N_DIFF_SEQ
, CONVERT(INT,NULL) AS BLOCK_NAME
INTO #T_CT_1
FROM PPM.M_CYT_STD_CT C
WHERE C.DIFF_SEQ IS NULL

EXEC P_COM_LOG @MODULE,'CYT_STEP_075', @START_TIME, 0

** SELECT DISTINCT C.MAIN_ROUTE_ID, C.OP_NO, C.SEQ, C.DIFF_SEQ, CONVERT(INT,NULL) AS N_DIFF_SEQ
, CONVERT(INT,NULL) AS BLOCK_NAME
INTO #T_CT_2
FROM PPM.M_CYT_STD_CT C
WHERE ISNULL(C.DIFF_SEQ,999999) <> (SELECT TOP 1 ISNULL(DIFF_SEQ,999999) FROM PPM.M_CYT_STD_CT
WHERE MAIN_ROUTE_ID = C.MAIN_ROUTE_ID
AND SEQ = C.SEQ-1)**

EXEC P_COM_LOG @MODULE,'CYT_STEP_076', @START_TIME, 0

SELECT DISTINCT T.MAIN_ROUTE_ID, T.OP_NO, T.SEQ, T.DIFF_SEQ, T.N_DIFF_SEQ,T.BLOCK_NAME 
INTO #T_CT
FROM 
(SELECT  MAIN_ROUTE_ID, OP_NO, SEQ, DIFF_SEQ, N_DIFF_SEQ,BLOCK_NAME
FROM #T_CT_1
UNION ALL
SELECT MAIN_ROUTE_ID, OP_NO, SEQ, DIFF_SEQ, N_DIFF_SEQ,BLOCK_NAME
FROM #T_CT_2) AS T

EXEC P_COM_LOG @MODULE,'CYT_STEP_077', @START_TIME, 0

DROP TABLE #T_CT_1
DROP TABLE #T_CT_2

请教有没有遇到这样的问题，该如何排查错误?

Answer 1

针对你的问题，可能的排查方式如下：

1. 查看网络连接情况：查看服务器与客户端之间的网络连接情况，是否存在网络延迟，如有可以尝试优化网络连接。

2. 检查SQL Server配置：检查SQL Server相关配置，例如TCP/IP协议是否启用，最大并发连接数等，是否存在配置不当导致的问题。

3. 查看服务、作业及进程状态：检查服务、作业及进程状态，例如SQL Server Agent是否正常运行，作业是否有权限执行等。

4. 检查存储过程逻辑：对存储过程进行逐行调试，检查每行SQL语句是否正常运行，是否存在错误或异常情况，以此来定位问题。

在排查问题时，建议进行逐步排查，先排除可能存在的基础问题，再逐步深入排查，最终找到问题原因并解决。