sql-server - 如何从 KILLED/ROLLBACK 状态挽救 SQL Server 2008 查询？

Question

我有一个存储过程，它将从某个查询中产生的数百万行批量插入到 SQL 数据库中。它有一个参数选择批处理；当省略此参数时，它将收集批处理列表并递归调用自身，以便迭代批处理。在(伪)代码中，它看起来像这样:

CREATE PROCEDURE spProcedure AS BEGIN
    IF @code = 0  BEGIN
        ...
        WHILE @@Fetch_Status=0 BEGIN
            EXEC spProcedure @code
            FETCH NEXT ... INTO @code
        END
    END
    ELSE BEGIN

        -- Disable indexes
        ...

        INSERT INTO table
        SELECT (...)

        -- Enable indexes
        ...

现在，这个过程可能会很慢，无论出于什么原因:它无法获得锁，它使用的索引之一被错误定义或禁用。在这种情况下，我希望能够终止该过程，截断并重新创建结果表，然后重试。但是，当我尝试终止该过程时，该过程经常会进入 KILLED/ROLLBACK 状态，并且似乎无法返回。从谷歌我学会了做sp_lock ，找到spid，然后用KILL <spid>杀死它。但当我试图杀死它时，它告诉我

SPID 75: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 554 seconds.

我确实找到了forum message暗示另一个 spid 应该在另一个 spid 开始回滚之前被杀死。但这对我来说也不起作用，而且我不明白，为什么会出现这种情况......可能是因为我递归地调用我自己的存储过程吗？ (但它应该具有相同的 spid，对吧？)

无论如何，我的进程只是坐在那里，死了，不响应终止，并锁定表。这是非常令人沮丧的，因为我想继续开发我的查询，而不是在我的服务器上等待数小时，同时假装正在完成所谓的回滚。

是否有某种方法可以告诉服务器不要为我的查询存储任何回滚信息？或者不允许任何其他查询干扰回滚，这样就不会花费这么长时间？或者如何以更好的方式重写我的查询，或者如何在不重新启动服务器的情况下成功终止进程？

Answer 1

一些评论。

首先，gbn 关于无法取消正在进行的回滚的说法是正确的。这就是 SQL 保持事务完整性的方式，您不希望这种行为发生改变。如果您完全不在乎，只想将数据库恢复到上次备份时的位置，请按照他的步骤操作。

但是，有一点需要注意。有时我看到 spid 并没有真正回滚，它只是卡住了(通常处于 0% 或 100% 进度)。在这种情况下，最可靠的指标是事件监视器中 spid 的 CPU/IO 计数器是否没有变化(并且 SPID 没有被另一个 SPID 阻止)。在这种情况下，您可能需要重新启动 SQL 服务(不需要完全重新启动)才能清除 spid。

关于重新组织查询以使这些回滚不会削弱您的能力，是的，这是可能的。只需使用显式事务即可:

    WHILE @@Fetch_Status=0 BEGIN
        BEGIN TRANS
            EXEC spProcedure @code
        COMMIT TRANS
        FETCH NEXT ... INTO @code
    END

数据在每个批处理后提交。如果您遇到问题并且必须终止 spid，它应该只回滚它正在处理的当前批处理。

如果单个批处理太多，您可能可以重构“spProcedure”以插入 10k-100k 记录的较小批处理，并在每个批处理后提交。