如何从巨大的表中删除过期数据而不让日志文件增长失控？
我有一个巨大的表（30 亿行），不幸的是其中大部分包含过期的数据。我想简单地删除所有这些过期的行，并保留其余的。
我可以执行这样的语句：
delete from giganticTable where exp_date < getDate()
执行计划以某种方式估计大约有 4 亿行将被删除。
执行时，不仅一个小时后还没有完成，而且数据库事务日志文件也从 6 GB 增长到 90 GB。请注意，发生这种情况时数据库处于批量日志恢复模式。我最终取消了这个查询，因为我确信一定有更好的方法来做到这一点。
我有几个表需要执行类似的操作。如果我完全不想恢复这些行，那么删除这些行的最快且最节省空间的方法是什么？
请注意，我使用的是 Microsoft SQL Server 2005。
I have a huge table (3 billion rows), which unfortunately contains mostly expired data. I want to simply delete all of these expired rows, and keep the rest.
I can execute a statement like this:
delete from giganticTable where exp_date < getDate()
The execution plan somehow estimates that about 400 million rows will be deleted.
When executed, not only does this not finish after an hour, but the database transaction log file is also growing from 6 GB to 90 GB. Note that the database was in bulk-logged recovery model while this is happening. I eventually canceled this query, since I'm sure there must be a better way to do this.
I have several tables that I need to perform a similar operation to. What's the fastest and most space-efficient way to just delete these rows if I have absolutely no desire to ever recover them?
Note that I'm using Microsoft SQL Server 2005.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我发现它在从包含大量行的表中进行删除以批量删除 5000 行左右的行时非常有用（我通常测试哪个值运行速度最快，有时是 5000 行，有时是 10,000 行等） 。这使得每个删除操作都能快速完成，而不是等待很长时间才能一条语句删除 4 亿条记录。
在 SQL Server 2005 中，类似的东西应该可以工作（当然，请先测试）：
我会看看批量删除对日志文件大小有何影响。如果它仍然炸毁日志，那么您可以尝试将恢复模型更改为 简单，删除记录，然后切换回批量记录，但前提是系统可以容忍丢失一些最近的数据。在尝试该过程之前我肯定会进行完整备份。此线程还建议您可以设置一个作业来备份日志仅指定截断，因此这可能是另一个选项。希望您有一个可以测试的实例，但我将从批量删除开始，看看这如何影响性能和日志文件大小。
I've found it useful when doing deletes from table with a large number of rows to delete rows in batches of say 5000 or so (I usually test to see which value works the fastest, sometimes it's 5000 rows, sometimes 10,000, etc.). This allows each delete operation to complete quickly, rather than waiting a long time for one statement to knock out 400 million records.
In SQL Server 2005, something like this should work (please test first, of course):
I would see what deleting in batches does to the log file size. If it is still blowing up the logs, then you could try changing the Recovery Model to Simple, deleting the records, and then switching back to Bulk Logged, but only if the system can tolerate the loss of some recent data. I would definitely make a Full Backup before attempting that procedure. This thread also suggests that you could setup a job to backup the logs with truncate only specified, so that could be another option. Hopefully you have an instance you can test with, but I would start with the batched deletes to see how that affects performance and the log file size.
当您想在表上做大量工作时，您真的不想尝试任何愚蠢的事情，例如关闭日志记录，因为长时间任务期间的任何问题都可能很容易导致数据库损坏和其他问题。但是，有一种方法可以解决您的问题。
创建一个与实际表的架构匹配的临时表。用您想要保留的数据填充它。然后，截断原始表（在日志文件上非常快速且简单）。最后，将数据从临时表中移出并移入原始（现在为空）表中。
如果您使用自动递增主键，则需要强制字段采用原始键（这样以后就不会出现问题）。
You really don't want to mess with trying anything silly like turning off logging when you want to do a lot of work on a table since any issues during the long task could easily lead to database corruption and other issues. However, there is a way around your issue.
Create a temp table that matches the schema of your real table. Populate it with the data you want to KEEP. Then, truncate the original table (extremely fast and easy on the log files). Finally, move the data out of the temp table and into your original (and now empty) table.
If you use auto-incrementing primary keys, you will need to force the field to take your original keys (so you don't have issues later).
你应该每天都做，这样你就不会一下子得到这么大的工作。
既然你处于这种情况，我的建议如下：
明确地写出日期，例如：
You should have done it daily, so you don't get such a huge job at once.
Since you are in the situation, here are my suggestions:
Write the date explicitly like: