主页 / dba / 问题 / 166112
Accepted
Alf47
Asked: 2017-03-04 08:47:36 +0800 CST

使用 CHECKPOINT 与 GO

  • 772

通常，当其中一位开发人员或数据分析师需要执行非常大的数据更新或删除时（由于要保留的数据集太大，截断或截断/插入没有意义）我建议他们这样做类似于以下内容：

-- Delete 1 million rows 1 thousand at a time
DELETE TOP (1000) FROM TableA WHERE <condition>
WAITFOR DELAY '00:00:01'
GO 1000

对于处于完全恢复模式的数据库，这样做的结果是 1) 等待允许其他事务在需要时进行处理，以及 2) 当日志备份运行时，它能够在日志文件中将已经完成的操作标记为脏操作，因此它可以重新使用空间并防止日志增长过快。

我不想这样做，而是想知道是否有可能使用检查点来完成同样的事情。该声明是否会有效地导致相同情况的发生？

-- Delete 1 million rows 1 thousand at a time
WHILE EXISTS ( SELECT 1 FROM TableA WHERE <condition> )
BEGIN
DELETE TOP (1000) FROM TableA WHERE <condition>
WAITFOR DELAY '00:00:01'
CHECKPOINT
END

同样，这些是完全恢复模式下的数据库。

sql-server delete

2 个回答

  1. Best Answer

    进行大量更新或删除时最重要的事情是避免事务日志增长失控。允许日志重用并避免膨胀到 Tlog（事务日志截断）

    • 在完全恢复模式下 - 进行日志备份
    • 在简单恢复模式下 - 做检查点。

    由于您使用的是完全恢复，因此您的删除逻辑应该是

    begin
 begin tran
   -- delete using batches 
   -- carefully choose batch size. SQL Server will do a lock escalation 
   -- for batch greater than 5000. (https://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx)
 commit tran / rollback tran 
  -- since you are using Full recovery, take log backups
  -- with CHECKPOINT SQL Server still cannot reuse those portions of the log until you perform a log backup.
end

    我waitfor delay特别喜欢。避免长时间阻塞。

    请参阅这篇金矿博文 - Aaron Bertrand将大型删除操作分成块。此外，Take Care When Scripting Batches是一篇非常有用的博文。

    • 2

  2. 两者是等价的，您将批处理与日志截断混淆了。

    重要的是代码分批运行，以便释放锁，其他会话可以跳入并读取表（并且当数据库处于简单恢复模型时日志文件可以清除）。

    但是 - 在您的第一个示例中， WAITFOR 可能不是必需的 - 所有会话都排队等待并且可能会在每次运行之间进入。

    第二个是等价的，只是它告诉 SIMPLE 模式数据库日志可以被清除，然而，这很可能每隔几秒发生一次！在 FULL 恢复模型中，两者都没有增加太多价值。

    • 1

相关问题