如何在T-SQL中删除大表的所有数据？

Question

我们遇到了一个有点奇怪的情况。基本上，在我们的一个数据库中有两个表，它们会接收大量我们不需要或关心的日志信息。部分原因是因为这个原因，我们的磁盘空间快用完了。

我正在尝试清理这些表，但这需要很长时间（即使在周末运行后仍然有57,000,000多条记录...而且那只是第一个表格！）

仅使用delete table需要很长时间，并占用驱动器空间（我认为是由于事务日志）。现在我正在使用while循环一次删除X条记录，同时调整X来确定最快的速度。例如，X=1000需要3秒，而X=100,000则需要26秒...做数学计算略微更快。

但问题是是否有更好的方法呢？

（完成后，将运行SQL代理作业每天清理该表...但需要先清除它。）

- CodeRedick

---

你是要删除表中的所有记录吗？ - Joe Phillips

我不知道GFI是什么...所以可能不是。 - CodeRedick

Answer 1

在删除之前请TRUNCATE表或禁用索引

TRUNCATE TABLE [tablename]

截断将从表中删除所有记录，并且不会单独记录每个删除操作。

- Joe Phillips

---

值得链接到http://en.wikipedia.org/wiki/Truncate_(SQL)，以确保他们理解其限制，但我完全同意这个建议。 - ShuggyCoUk

值得一提的是，对于“IDENTITY”列，使用“TRUNCATE”意味着下一个插入的值将获得表“SEED”的标识值（通常为1）。这可能并不总是理想的。 - Oliver

Answer 2

除了其他回答之外，如果您想保留过去一天（或过去一个月或一年等）的数据，则保存该数据，执行TRUNCATE TABLE，然后将其重新插入原始表中：

SELECT
     *
INTO
     tmp_My_Table
FROM
     My_Table
WHERE
     <Some_Criteria>

TRUNCATE TABLE My_Table

INSERT INTO My_Table SELECT * FROM tmp_My_Table

下一步要做的是问问自己，如果没有人在意，为什么要将所有这些信息记录到日志中。如果你真的不需要这些信息，那么就把源头的日志记录关闭。

- Tom H

Answer 3

1) 截断表

2) 将表导出为脚本，删除并重新创建表

- SQLMenace

Answer 4

TRUNCATE TABLE [tablename]

将会在不记录日志的情况下删除所有记录。

- DJ.

---

它将执行一些最小化的记录，仅记录所影响的页面和区段。 - SQLMenace

Answer 5

根据您想要保留的程度，您可以将所需记录复制到临时表中，清空日志表，然后将临时表记录复制回日志表。

- John M Gant

Answer 6

请检查此内容

1. 来自 MSDN 的文章 删除大量数据
2. 恢复模式的信息
3. 以及查看或更改数据库的恢复模式

- Icarus

Answer 7

如果您能找出最佳的x值，这将以最快的速度循环删除。设置rowcount可以限制每个循环步骤中将被删除的记录数。如果日志文件太大，请在循环中放置一个计数器，并每一百万行截断一次。

set @@rowcount x while 1=1 Begin

delete from table If @@Rowcount = 0 break

End

将数据库的日志模式更改为简单或批量记录将减少一些删除开销。

- u07ch