Ric Sierra (9/15/2009)

---

Just... keep simple!

If you need to delete a large number of rows and the scenario don't need to keep the database log.

In this case I want to delete all the rows between 2009/01/01 and 2009/10/23, about 100000000 rows.

delete top (10000) from myTable where colDate between '20090101' and '20091023'
backup log myDatabase with no_log
go 10000


What you think?


P.S. The recovery model is SIMPLE.

Lynn Pettis (9/15/2009)

---

If your database is using the SIMPLE recovery model you can't do BACKUP LOG, nor is it needed. The only other issue I'd have with your simple process is why run it more than needed. Using the GO 10000, your delete statement is going to run 10000 times regardless of how many times it really needs to run.

If your database is using BULK-LOGGED or FULL recovery model, then you do need the transaction log backups in case a recovery is needed. The code I have provided can be incorporated into a stored procedure where periodic deletes of large nmber of records need to be accomplished.

Ric Sierra (9/15/2009)

---

In this scenario I need to delete 100,000,000 rows, by each batch I delete 10,000 rows but to ensure to delete all the rows I need to run 10,000 times the batch.
10,000 x 10,000 = 100,000,000

Lynn Pettis (9/15/2009)

---

That was not obvious based on your original post. There was nothing there to indicate how many rows were to be deleted.

Lynn Pettis (9/15/2009)

---

That was not obvious based on your original post. There was nothing there to indicate how many rows were to be deleted. Please remember, my code makes no determination regarding the total number of rows to be deleted. The delete in the example code is based on a data range, and if you look at the number of records generated in the sample dataset, there is no specific number of records for each year, it is rendomly generated which is much more likely in a real world environment. You aren't always going to be deleting a set number of rows each time.

robblackin (9/15/2009)

---

I just had to deal with this. However, I need to have an hourly sliding window where I delete records older than 3 hours for working with .net viewstate.

I had the transaction log in simple mode and was deleting in small batches. However, the deletes were not keeping up with the inserts. Since it is viewstate the records are very big.

What I did was set up a partitioned view and just truncate the table in the view thereby deleting 200,000 records in less than a second and not affect the inserts.

IMHO, truncating partitions or tables in a partitioned view is much cleaner and works faster than this method.