Sam (s0***@go.com) writes:
I have a database that is 70GB big. One of the tables has over 350
million rows of data. I need to delete about 1/3 of the data in that
one table.
I was going to use a simple delete command to delete the unnessacay
data.
Something like
Delete from tablename where columname > '100'
However, it takes SOOO LONG to run, over 8 hours, if not longer....
What is the best way to delete this data???
One idea was to create a Temp Table, then move the needed data to the
temp table, then Truncate the Table, and move that data back.
Since you are keeping two thirds of the data, that is likely to take
even longer time.
A better strategy is to take the operation in portions. If you are using
simple recovery, SQL Server will truncate the log between the rounds.
If you are using full or bulk-logged you have to truncate yourself.
Whichever, don't forget to take a full backup when you're done. The
significance of the log here, is that you avoid costly autogrows of
the log.
To do it portions, there are a couple of strategies.
If there is key column which determins the delete condition, you
can use this for iteration. But the simplest is probably to do:
SET ROWCOUNT 100000
WHILE 1 = 1
BEGIN
DELETE tbl WHERE ...
IF @@rowcount < 100000
BREAK
END
SET ROWCOUNT 0
Also, watch out for these things:
* Drop all indexes on the table that are not good for finding the rows
to delete. Reapply the indexes when you are done.
* If there is a trigger on the table, use ALTER TABLE DISABLE TRIGGER.
Don't forget to enable when you are done. And check that the trigger
does not perform any cascading updates or deletes.
* If the table is referenced by a foreign key in another table, make
sure that that referencing table has an index on that column.
--
Erland Sommarskog, SQL Server MVP,
so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp