stored-procedures - 使用 SQL 代理作业在循环中调用过程

Question

我正在 SQL 企业管理器 2000 上组合一个作业来复制和删除几个数据库表中的记录。我们已经运行了一个直接的大量复制和删除存储过程，但它可能会在数百万行上运行它，因此会挂起服务器。我有兴趣尝试一次以 100 个记录块运行该服务，因此服务器不会停止(这是一个实时 Web 数据库)。我希望这项服务每晚运行一次，这就是我将它放在代理工作中的原因。有什么方法可以循环调用实际执行复制和删除的存储过程，然后在每次调用之间“休眠”以使服务器有时间 catch ？我知道有 WAITFOR 命令，但我不确定这是否会保留处理器或让它同时运行其他查询。

谢谢!

Answer 1

“分块”删除是删除过多数据而不会使事务日志文件膨胀的首选方法。 BradC 的帖子就是一个合理的例子。

最好在单个存储过程中管理此类循环。为了随着时间的推移传播这些工作，我仍然会将它保留在程序中。如果您认为有必要处理可能的并发问题，则在循环中插入 WAITFOR 将在每组删除之间放置一个“暂停”。使用 SQL 代理作业来确定过程何时开始——如果您需要确保它在某个时间停止，也可以将其放入循环中。

我对这段代码的看法是:

--  NOTE: This is a code sample, I have not tested it
CREATE PROCEDURE ArchiveData

    @StopBy DateTime
    --  Pass in a cutoff time.  If it runs this long, the procedure will stop.
AS

DECLARE @LastBatch  int

SET @LastBatch = 1
--  Initialized to make sure the loop runs at least once


WHILE @LastBatch > 0
 BEGIN

    WAITFOR DELAY '00:00:02'
    --  Set this to your desired delay factor

    DELETE top 1000  --  Or however many per pass are desired
     from SourceTable
    --  Be sure to add a where clause if you don't want to delete everything!

    SET @LastBatch = @@rowcount

    IF getdate() > @StopBy
        SET @LastBatch = 0

 END

RETURN 0

唔。重读您的帖子意味着您想在删除数据之前先将数据复制到某处。为此，我设置了一个临时表，在循环内部首先截断临时表，然后复制前 N 个项目的主键，通过连接到临时表插入到“归档”表中，然后也通过连接到临时表删除源表。 (只是比直接删除复杂一点，不是吗？)