sql - 如何在不让日志文件失控的情况下从大表中删除过期数据？

Question

我有一个巨大的表(30 亿行)，不幸的是其中大部分包含过期的数据。我想简单地删除所有这些过期的行，并保留其余的行。

我可以执行这样的语句:

delete from giganticTable where exp_date < getDate()

执行计划以某种方式估计将删除大约 4 亿行。

执行时，不仅在一个小时后还没有完成，而且数据库事务日志文件也从 6 GB 增长到 90 GB。请注意，发生这种情况时，数据库处于大容量日志恢复模式。我最终取消了这个查询，因为我确信一定有更好的方法来做到这一点。

我有几个表需要对其执行类似的操作。如果我绝对不想恢复它们，那么删除这些行的最快和最节省空间的方法是什么？

请注意，我使用的是 Microsoft SQL Server 2005。

Answer 1

我发现在从包含大量行的表中进行删除以批量删除行时很有用，比如 5000 左右(我通常测试哪个值运行最快，有时是 5000 行，有时是 10,000 行，等等) .这使得每个删除操作都可以快速完成，而不是等待很长时间才能通过一条语句删除 4 亿条记录。

在 SQL Server 2005 中，这样的事情应该可以工作(当然，请先进行测试):

WHILE EXISTS ( SELECT * FROM giganticTable WHERE exp_date < getDate())
BEGIN
  DELETE TOP(5000) FROM giganticTable WHERE exp_date < getDate()
END

我会看到批量删除对日志文件大小的影响。如果它仍然炸毁日志，那么您可以尝试将恢复模型更改为 Simple ，删除记录，然后切换回 Bulk Logged，但前提是系统可以容忍丢失一些最近的数据。在尝试该过程之前，我肯定会进行完整备份。此 thread还建议您可以设置一个作业来备份仅指定 truncate 的日志，因此这可能是另一种选择。希望你有一个可以测试的实例，但我将从批量删除开始，看看它如何影响性能和日志文件大小。