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

发布于 2024-08-07 23:57:28 字数 299 浏览 6 评论 0原文

我正在 SQL Enterprise Manager 2000 上整理一项工作，以复制和删除几个数据库表中的记录。我们已经运行了直接批量复制和删除存储过程，但它可能会在数百万行上运行，因此会挂起服务器。我有兴趣尝试一次以 100 个左右的记录块运行该服务，这样服务器就不会停止运行（这是一个实时 Web 数据库）。我希望这项服务每晚运行一次，这就是我将其置于代理工作中的原因。有没有办法循环调用实际执行复制和删除的存储过程，然后在每次调用之间“睡眠”，以便让服务器有时间赶上？我知道有 WAITFOR 命令，但我不确定这是否会保留处理器或让它同时运行其他查询。

谢谢！

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

摘星┃星的人 2024-08-14 23:57:29

“分块”删除是删除过量数据而不会使事务日志文件膨胀的首选方法。 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 个项目的主键，通过临时表的联接插入到“存档”表中，然后也通过连接到临时表来删除源表。（只是比直接删除复杂一点，不是吗？）

"Chunkifying" your deletes is the preferred way to delete excessive amounts of data without bloating up transaction log files. BradC's post is a reasonable example of this.

Managing such loops is best done within a single stored procedure. To spread such work out over time, I'd still keep it in the procedure. Inserting a WAITFOR in the loop will put a "pause" between each set of deletes, if you deem that necessary to deal with possible concurrency issues. Use a SQL Agent job to determine when the procedure start--and if you need to make sure it stops by a certain time, work that into the loop as well.

My spin on this code would be:

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

Hmm. Rereading you post implies that you want to copy the data somewhere first before deleting it. To do that, I'd set up a temp table, and inside the loop first truncate the temp table, then copy in the primary keys of the TOP N items, insert into the "archive" table via a join to the temp table, then delete the source table also via a join to the temp table. (Just a bit more complex than a straight delete, isn't it?)

回复收藏 0 原文

我纯我任性 2024-08-14 23:57:29

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

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

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

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

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

Don't worry about waiting between loops, SQL server should handle the contention between your maintenance job and the regular activity on the server.

What really causes the problem in these types of situations is that the entire delete process happens all at once, inside a single transaction. This blows up the log for the database, and can cause the kinds of problems it sounds like you are experiencing.

Use a loop like this to delete in manageable chunks:

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

You can use similar logic for your copy.

回复收藏 0 原文