在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.

我们遇到了一个有点奇怪的情况。基本上，在我们的数据库中有两张表是我们不需要或不关心的。部分原因是我们的磁盘空间不够用了。

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!)

我正在努力清理数据表，但这需要花费很长时间(在这个周末过后，还有5700多万的记录……)这只是第一张桌子!)

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.

仅仅使用delete table就会占用永远的空间，占用硬盘空间(我相信是因为事务日志)。现在我使用while循环一次删除记录X，同时使用X来确定什么是最快的。例如X=1000需要3秒，X=100,000需要26秒……计算起来稍微快一点。

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

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

(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.)

(完成之后，将运行SQL Agent作业，每天清理表一次……)但需要先清理。

7 个解决方案

#1

TRUNCATE the table or disable indexes before deleting

删除表或禁用索引之前删除。

TRUNCATE TABLE [tablename]

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

截断将从表中删除所有记录，而不分别记录每个删除。

#2

To add to the other responses, if you want to hold onto the past day's data (or past month or year or whatever), then save that off, do the TRUNCATE TABLE, then insert it back into the original table:

要添加其他响应，如果您想保留过去一天的数据(或过去一个月或一年或其他)，那么将其保存，执行truncatetable，然后将其插入原始表:

SELECT
     *
INTO
     tmp_My_Table
FROM
     My_Table
WHERE
     <Some_Criteria>

TRUNCATE TABLE My_Table

INSERT INTO My_Table SELECT * FROM tmp_My_Table

The next thing to do is ask yourself why you're inserting all of this information into a log if no one cares about it. If you really don't need it at all then turn off the logging at the source.

接下来要做的就是问自己，如果没有人关心，为什么要将所有这些信息插入日志中。如果您真的根本不需要它，那么请关闭源代码的日志记录。

#3

1) Truncate table

1)截断表

2) script out the table, drop and recreate the table

2)在表中编写脚本，删除并重新创建表

#4

TRUNCATE TABLE [tablename]

will delete all the records without logging.

将删除所有记录而不进行日志记录。

#5

Depending on how much you want to keep, you could just copy the records you want to a temp table, truncate the log table, and copy the temp table records back to the log table.

根据需要保留的数量，可以将想要保存的记录复制到临时表，截断日志表，并将临时表记录复制回日志表。

#6

If you can work out the optimum x this will constantly loop around the delete at the quickest rate. Setting the rowcount limits the number of records that will get deleted in each step of the loop. If the logfile is getting too big; stick a counter in the loop and truncate every million rows or so.

如果你能算出最优的x，这将以最快的速度循环删除。设置行数限制将在循环的每个步骤中删除的记录数量。如果日志文件太大;在循环中插入一个计数器并截断每一百万行左右。

set @@rowcount x while 1=1 Begin

设置@@rowcount x, 1=1开始

delete from table If @@Rowcount = 0 break

如果@@Rowcount = 0中断，则从表中删除

End

结束

Change the logging mode on the db to simple or bulk logged will reduce some of the delete overhead.

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

#7

check this

检查这个

article from MSDN Delete_a_Huge_Amount_of_Data_from
文章从MSDN Delete_a_Huge_Amount_of_Data_from
Information on Recovery Models
复苏的信息模型
and View or Change the Recovery Model of a Database
查看或更改数据库的恢复模型

#1