Delete 130 million rows

Question

In SQL Server 2012, I need to delete 100+ million rows from a table with 4-5 indices.

How can I proceed? I guess that the transaction log will be full and thus cancel the transaction.

Thanks for your help

Are you deleting more than 50% of the rows, or fewer than 50%. (different strategies for each). — tgolisch, Mar 21 '19 at 16:00
Gordon was quicker than me. If you are deleting more than half, it is usually better to copy the data (that you want to keep) to a new table (without indexes), then apply indexes after the data is moved. Drop the old one, rename the new one. — tgolisch, Mar 21 '19 at 16:02

score 3 · Answer 1 · answered Mar 21 '19 at 16:00

3

Depending on the size of the table, you might find it faster to empty and repopulate:

create table temp_t
    select *
    from t
    where <i want to keep conditions>;

truncate table t;

insert into t
    select *
    from temp_t;

In the longer term, you may want to add partitions so it is easier to delete rows. For instance, partitioning by date would allow you to drop old records with a minimum of overhead.

answered Mar 21 '19 at 16:00

Gordon Linoff

1,242,037
58
646
786

it's already partionned by date. i need to drop every date older than 3 months and not that is not an end of month. I already have a view for valid dates so i do delete where not in valid dates but again there is too much to delete – Badr Erraji Mar 21 '19 at 16:03
2

If you cannot use this method you can read through this post by Aaron Bertrand about batch deletes and view his test results. https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes – Joe Mar 21 '19 at 16:09

score 0 · Answer 2 · answered Mar 21 '19 at 16:09

If you

use the simple recovery model and
don't need to delete all those rows in a single transaction,

it should suffice to do a chunked delete. Lots of examples can be found in various related questions here on SO and on dba.SE (e.g. this one or that one). To make this answer self-contained, I will quote one of those examples (taken from this answer by gbn and slightly modified) here:

SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
    DELETE TOP (10000) MyTable WHERE <some condition>

Delete 130 million rows

2 Answers2