在 t-sql 中删除所有大表的最佳方法是什么? [英] What is the best way to delete all of a large table in t-sql?

问题描述

我们遇到了一个有点奇怪的情况.基本上，我们的一个数据库中有两个表，它们提供了大量我们不需要或不关心的日志信息.部分原因是我们的磁盘空间不足.

We've run across a slightly odd situation. Basically there are two tables in one of our databases that are fed tons and tons of logging info we don't need or care about. Partially because of this we're running out of disk space.

我正在尝试清理表格，但它需要永远的时间(在让这个通过周末之后仍然有 57,000,000 多条记录......而这只是第一张桌子！)

I'm trying to clean out the tables, but it's taking forever (there are still 57,000,000+ records after letting this run through the weekend... and that's just the first table!)

仅使用删除表会永远占用并占用驱动器空间(我相信是因为事务日志.)现在我正在使用 while 循环一次删除记录 X，同时使用 X 来确定什么是其实最快.例如，X=1000 需要 3 秒，而 X=100,000 需要 26 秒……计算速度稍快.

Just using delete table is taking forever and eats up drive space (I believe because of the transaction log.) Right now I'm using a while loop to delete records X at a time, while playing around with X to determine what's actually fastest. For instance X=1000 takes 3 seconds, while X=100,000 takes 26 seconds... which doing the math is slightly faster.

但问题是是否有更好的方法?

But the question is whether or not there is a better way?

(完成此操作后，要运行 SQL 代理作业，每天清理一次表...但需要先将其清理.)

(Once this is done, going to run a SQL Agent job go clean the table out once a day... but need it cleared out first.)

推荐答案

TRUNCATE表或删除前禁用索引

TRUNCATE the table or disable indexes before deleting

TRUNCATE TABLE [tablename]

截断将从表中删除所有记录，而不单独记录每个删除.

Truncating will remove all records from the table without logging each deletion separately.

这篇关于在 t-sql 中删除所有大表的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！