SQL Server中捕获用户手动中止存储过程场景以完善审计记录

针对用户手动中止存储过程导致审计记录停留在Running状态的问题，可通过以下几种方案逐步完善审计流程：

方案一：TRY...CATCH 结合 XACT_ABORT 捕获用户中止错误 #

用户通过SSMS手动停止存储过程时，SQL Server会抛出特定错误（如错误号121：The query has been canceled by the user.）。通过在存储过程中启用SET XACT_ABORT ON并结合TRY...CATCH块，可捕获这类中断事件并更新审计状态。

CREATE PROCEDURE YourTargetProcedure
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON; -- 确保严重中断触发CATCH逻辑

    DECLARE @AuditID INT;
    DECLARE @CurrentSessionID INT = @@SPID;

    -- 1. 插入初始审计记录，同时存储会话ID
    INSERT INTO Audit (StartTime, Status, SessionID)
    VALUES (GETDATE(), 'Running', @CurrentSessionID);
    SET @AuditID = SCOPE_IDENTITY();

    BEGIN TRY
        -- 存储过程核心业务逻辑
        -- ...

        -- 2. 无错误完成，更新状态为Complete
        UPDATE Audit
        SET Status = 'Complete', EndTime = GETDATE()
        WHERE AuditID = @AuditID;
    END TRY
    BEGIN CATCH
        DECLARE @ErrorNum INT = ERROR_NUMBER();
        DECLARE @ErrorMsg NVARCHAR(4000) = ERROR_MESSAGE();

        -- 匹配用户中止相关错误号，可根据实际环境补充
        IF @ErrorNum IN (121, 233, 0)
            UPDATE Audit
            SET Status = 'Canceled', EndTime = GETDATE(), ErrorDetails = @ErrorMsg
            WHERE AuditID = @AuditID;
        ELSE
            UPDATE Audit
            SET Status = 'Error', EndTime = GETDATE(), ErrorDetails = @ErrorMsg
            WHERE AuditID = @AuditID;

        -- 可选：重新抛出错误，保留原有错误抛出逻辑
        THROW;
    END CATCH
END

方案二：服务器级会话终止触发器（补充场景） #

如果遇到强制断开连接等TRY...CATCH无法覆盖的场景，可创建服务器级触发器捕获会话终止事件，更新对应审计记录。需注意该方案需要服务器级权限，且需在Audit表中存储会话ID（SessionID）字段。

CREATE TRIGGER AuditSessionDisconnectTrigger
ON ALL SERVER
FOR DISCONNECT
AS
BEGIN
    SET NOCOUNT ON;

    -- 更新当前终止会话中未完成的审计记录
    UPDATE a
    SET a.Status = 'Canceled', a.EndTime = GETDATE()
    FROM Audit a
    WHERE a.SessionID = @@SPID
      AND a.Status = 'Running';
END

方案三：定期清理作业（兜底方案） #

创建SQL Server Agent定期作业，扫描并标记超时的Running状态审计记录，避免无效记录长期存在。例如，标记超过1小时未完成的记录为Stale：

-- 定期执行的清理脚本
UPDATE Audit
SET Status = 'Stale', EndTime = GETDATE()
WHERE Status = 'Running'
  AND StartTime < DATEADD(HOUR, -1, GETDATE());

方案优先级建议 #

1. 优先使用方案一：存储过程内处理，实时性强且权限要求低，覆盖绝大多数手动中止场景；
2. 用方案二作为补充：处理强制断开连接等极端场景；
3. 方案三作为兜底：确保审计数据的准确性和整洁性。

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