磁盘空间不足时从表中删除多行

I need to delete about 5 million rows from a table that has about 6 million rows because I'm out of disk space. Is there a quick way to delete them? I've tried calling delete on batches, but it takes a very long time to run and sometimes it throws an error because of locks and doesn't do anything.

我需要从一个有大约600万行的表中删除大约500万行,因为我没有磁盘空间。有没有快速删除它们的方法?我试过在批处理上调用delete,但是运行需要很长时间,有时它会因为锁而抛出错误而且什么都不做。

Edit:

I was doing a query that's something like

我正在做一个类似的查询

delete from <table> where updated_timestamp < '2012-02-20'

Now, to get smaller batches, the query is the following

现在,为了获得更小的批次,查询如下

delete from <table> where id < [100000 row increments]

The error I was getting was that it failed to get a lock on the table. I don't have the exact text at the moment, but if I run into it again, I'll paste it here

我得到的错误是它无法锁定表。我目前没有确切的文字,但如果我再次遇到它,我会把它粘贴在这里

4 个解决方案

#1

The only way to delete rows is to either DELETE FROM <table> WHERE <some condition>, or delete all rows with either TRUNCATE TABLE <table> or DROP TABLE <table> (and then re-create it).

删除行的唯一方法是DELETE FROM

,或者使用TRUNCATE TABLE WHERE

或DROP TABLE

删除所有行(然后重新创建它)。

You've provided no information that can allow any more specific an answer than that, I'm afraid. You can do it in smaller sets of rows (batches) by using the condition in the WHERE clause. ("Sometimes throws an error" isn't useful in helping you to solve it, BTW, because "throws an error" is meaningless without information about what "an error" might be.)

我担心,你没有提供任何可以提供更具体答案的信息。您可以使用WHERE子句中的条件在较小的行集(批处理)中执行此操作。 (“有时会抛出错误”对帮助你解决它没有用,顺便说一下,因为“抛出错误”没有关于“错误”可能是什么的信息是没有意义的。)

#2

My suggestion in your situation is select the million records that you want to keep into a solid "temp" table, then truncate the table because when you delete the records it records all the records that you delete into the log which will take extra disk space. After you have truncate the table you add the records from the solid "temp" table back into the original table and drop the solid "temp" table.

在你的情况下我的建议是选择你想保留在一个坚实的“临时”表中的百万条记录,然后截断表,因为当你删除记录时它会记录你删除到日志中的所有记录,这将占用额外的磁盘空间。截断表后,将实体“temp”表中的记录添加回原始表中,然后删除实体“temp”表。

Something else that you can do is to run a CHECKPOINT on the database, and then shrink it. It should free some space on the disk.

您可以做的其他事情是在数据库上运行CHECKPOINT,然后将其缩小。它应该释放磁盘上的一些空间。

#3

The previous two answers are absolutely correct, and you should credit them for it.

前两个答案绝对正确,你应该相信它。

What you're looking for is the following

您正在寻找的是以下内容

SELECT * INTO into #temp FROM <table> WHERE updated_timestamp >= '2012-02-20'
TRUNCATE TABLE <table>
INSERT INTO <table> SELECT * FROM #temp

This becomes a little more complicated if you (a) have foreign keys referencing the table, or (b) have identity columns. For the former, you need to delete all records referencing records in the table via foreign key constraints, drop the constraint, then truncate and recreate the foreign keys. For the latter, you can use the following for the insert statement:

如果你(a)有外键引用表,或(b)有标识列,这会变得有点复杂。对于前者,您需要通过外键约束删除引用表中记录的所有记录,删除约束,然后截断并重新创建外键。对于后者,您可以使用以下内容作为insert语句:

SET IDENTITY INSERT <table> ON
INSERT INTO <table> (<field_list>)
SELECT <field_list>) FROM #temp
SET IDENTITY INSERT <table> ON

#4

Here is code for deleting log table, where log is older than one year. with batch loop.

以下是删除日志表的代码,其中日志超过一年。批量循环。

declare @batch int
declare @i int
declare @j int 
set @batch = 1000 
set @j = (select (COUNT(*)/@batch) + 1  from LOG_TABLE 
                where ACCESS_DATE <  dateadd(year,-1,getdate()))
set @i = 0
print @j

while (@i < @j)
BEGIN



delete top (@batch)
from LOG_TABLE 
where ACCESS_DATE < dateadd(year,-1,getdate())
SET @i = @i + 1
END

#1