I need to delete thousands of rows in a table.
What's the best way to do it without filling the transaction log file?

Thanks,

Felipe

You can delete on batches. Here is example with 2000 rows per batch. You
need to add the correct WHERE clause filters (requires SQL Server
2005/2008).

WHILE 1 = 1
BEGIN

   DELETE TOP(2000)
   FROM Table
   WHERE ...;

   IF @@ROWCOUNT < 2000
      BREAK;

END

Felipe,

Deleting a lot of rows (usually more than thousands) requires transaction
log management.Assuming that your standard maintenance of the transaction
logs will not meet this particular need you can:

WHILE ...  all desired rows are still not deleted
  BEGIN
     DELETE a safe number of rows
     -- If your database is not in simple recovery mode
     BACKUP LOG xxx ...
  END

Or, if you don't really want the transaction logs during this period.

ALTER DATABASE xxx SET RECOVERY_OPTION = 'SIMPLE'
WHILE ...  all desired rows are still not deleted
  BEGIN
     DELETE a safe number of rows
  END
ALTER DATABASE xxx SET RECOVERY_OPTION = 'FULL'
BACKUP DATABASE xxx ...

Now, let's think about how much space a deletion might need in the
transaction logs, just using made up numbers.

10,000,000 rows, average length 500 bytes,
several indexes (let's say about 20% more storage overhead)

5,000 megabytes of data
1,000 megabytes of indexes
-----
6,000 megabytes plus some additional overhead

So, if you know how much you are deleting, you can plan the size of each
deletion, the frequency of log backups, and so forth.

RLF


Show quoteHide quote

If you are getting rid of all the rows you may want to use TRUNCATE TABLE,
if there are only a few rows you wish to save then you could just copy them
and get rid of the rest. Other options may be to use partitions and drop
them when they are not needed.

John

Backup multi database
bak file = 5gb, attempted restore claims its 100Gb
SQL Server 2005 Performance
Transactional data fun...
using DATEADD to report informaiton from 60 days ago
Large table maintenance
Query timeout
truncate log
permission question
SQL Server 2005 SP3 and SQL Authentication