2

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?

Improve this question
3
  • Are you trying to empty the tables completely, or just remove some of the older data?
    – Yuck
    Jan 24, 2012 at 19:17
  • Removing older data, not empty the tables completely.
    – Sean
    Jan 24, 2012 at 19:22
  • This is off topic but I have to ask, how large is your transaction log compared to your database? Are you doing transaction log backups which will keep the log small or only database backups which will let the log grow until you run out of space? I only mention it because often when people run out of disk space it is because they are not managing their transaction log properly.
    – HLGEM
    Jan 24, 2012 at 23:32

1 Answer 1

Reset to default
7

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.

Improve this answer
7
  • Anyone care to explain the DV?
    – JNK
    Jan 24, 2012 at 19:18
  • Probably mvrak retaliating for someone DV his answer.
    – Yuck
    Jan 24, 2012 at 19:19
  • Creating a new table with 75 million rows might be impossible if the disk is full. /explained
    – mvrak
    Jan 24, 2012 at 19:19
  • @mvrak - A - it would only be 5m rows at a time since you want one lookup table for each table to be deleted from. B - these tables are tiny since there is only one or two fields. C - SQL server reuses empty space within allocated space in the existing files for stuff like this so it probably won't cause any growth at all.
    – JNK
    Jan 24, 2012 at 19:21
  • @mvrak all you explained is that you don't really know what you are talking about. Thanks for trying though!
    – JNK
    Jan 24, 2012 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.