代码之家 › 专栏 › 技术社区 › littlegreen

如何将SQL server 2008查询从终止/回滚状态中恢复?

locking stored-procedures sql-server-2008 sql-server

littlegreen · 技术社区 · 14 年前

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
        ...

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

SPID 75:事务回滚完成率:0%。预计时间剩余时间:554秒。

我确实找到了一个 forum message 暗示应该在另一个spid开始回滚之前杀死另一个spid。但那对我也不起作用,加上我不明白,为什么会是这样。。。可能是因为我递归调用自己的存储过程吗(但它应该有相同的spid,对吗?)

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

6 回复 | 直到 14 年前

BradC 14 年前

一些评论。

希望这种行为改变了。如果你完全不在乎,只想把数据库恢复到上次备份时的状态,那么就按照他的步骤做。

不是真的在后退吗

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

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

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

gbn 14 年前

即使重新启动实例,它也会继续这样做。

如果将9900万行插入或删除为1亿行,则需要回滚所有9900万行。您无法更改此行为。任何一条DML语句都是原子的。

如果要修复它:

删除数据库文件
启动SQL Server
使数据库处于损坏状态
还原

ChrisLively 14 年前

第一次通过它运行了大约8个小时,然后一个自动备份作业终止了该进程并使服务器跳转。它花了将近2天的时间重新上线,这样我们就可以重新开始这个过程。。这次确保备份过程已关闭。

Andrew 14 年前

据我所知,如果不做一些令人讨厌的事情(如硬重置),可能会破坏数据文件的一致性,那么SQL将进入恢复状态,并且仍然执行回滚操作,以确保回滚的事务成功回滚。

除非像nolock这样的东西可以让您通过现有的锁(您没有提到它是否是独占锁),否则您可能仍然可以编写表的模式脚本,使其成为MyTable2,然后继续编写查询,并在完成后返回并更改它们。

BradC 14 年前

当您终止一个SQL Server进程时,它不会立即终止,必须首先回滚该进程的活动事务所做的所有工作。回滚可能需要大量的时间—可能与kill之前查询在执行中使用的时间相同,甚至更多。

你可以采取一些措施来避免这种情况,但我不想在不了解你的需求和情况的情况下推荐这样的做法,因为这可能会对其他过程/查询产生不利影响。

Alocyte 10 年前

其他两个被该SPID阻塞的SPID;我杀了他。

反向逻辑-SPID被SPID阻塞,阻塞SPID回滚。