使用SQL代理作业调用循环中的过程

Question

我正在将SQL Enterprise Manager 2000上的作业放在一起以复制和删除几个数据库表中的记录。我们运行了一个直接批量复制和删除存储过程，但它可能会在数百万行中运行它，因此会挂起服务器。我有兴趣尝试一次运行100-ish记录块，因此服务器不会停下来（这是一个活动的Web数据库）。我希望这个服务每晚运行一次，这就是为什么我把它放在代理工作中。有什么方法可以将调用循环到实际进行复制和删除的存储过程，然后在每次调用之间“休眠”以使服务器有时间赶上？我知道有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 

嗯。重新读你的帖子意味着你想在删除它之前先将数据复制到某个地方。为此，我将设置一个临时表，并在循环内首先截断临时表，然后复制TOP N项的主键，通过连接到临时表中插入到“存档”表中，然后通过加入临时表来删除源表。 （只是比直接删除更复杂一点，不是吗？）

Answer 2

WAITFOR会让其他进程“走开”。我已经使用这种技术来阻止大DELETE锁定机器。创建一个WHILE循环，删除一行数据块，然后等待几秒钟（或者更少，只要是合适的）。

Answer 3

不要担心在循环之间等待，SQL服务器应该处理维护作业和服务器上常规活动之间的争用。

在这些类型的情况下，真正导致问题的原因是整个删除过程在一次事务中同时发生。这会炸毁数据库的日志，并可能导致您听到的各种问题，如您所遇到的情况。

使用这样一个循环来删除管理的块：

DECLARE @i INT 
SET @i = 1 

SET ROWCOUNT 10000 

WHILE @i > 0 
BEGIN 
    BEGIN TRAN 
     DELETE TOP 1000 FROM dbo.SuperBigTable 
     WHERE RowDate < '2009-01-01' 
    COMMIT 

    SELECT @i = @@ROWCOUNT 
END 
SET ROWCOUNT 0 

您可以使用类似的逻辑，你的副本。