Sql-server – SQL Drive getting full delete millions of rows

sql server

I'm looking for the easiest way to accomplish this in the limited hard drive space that I have.

I have a SQL drive that is dangerously close to getting full. We have about 15 tables that have over 20 million rows in them (these are high read/write tables on a 24/7 database).

After running a few queries, it's determined that we can deleted about 5 million rows each from the 15 tables.

What is the safest way to delete all these rows from the tables without causing the log file to fill the rest of the hard drive space?

Best Answer

Batch deletion is your best bet.

If possible, I would do something like:

<create lookup table with a list of PKs or keys of deletable rows>

WHILE 1=1
BEGIN
    DELETE TOP (100000)
    FROM MyBigTable T
    INNER JOIN MyLookupTable L
          ON T.PK = L.PK
    IF @@ROWCOUNT < 100000 BREAK;
END

Batching will keep your log use low, and using the INNER JOIN will reduce tempdb usage since you did the work of filtering in creating the first table.

Related Question