Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Feb 13 '12 at 22:08

This question came from our site for professional and enthusiast programmers.

    
Are you trying to empty the tables completely, or just remove some of the older data? –  Yuck Jan 24 '12 at 19:17
    
Removing older data, not empty the tables completely. –  Sean Jan 24 '12 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 '12 at 23:32

1 Answer 1

up vote 7 down vote accepted

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.

share|improve this answer
    
Anyone care to explain the DV? –  JNK Jan 24 '12 at 19:18
    
Probably mvrak retaliating for someone DV his answer. –  Yuck Jan 24 '12 at 19:19
    
Creating a new table with 75 million rows might be impossible if the disk is full. /explained –  mvrak Jan 24 '12 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 '12 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 '12 at 19:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.