Ric Sierra (9/15/2009)
Lynn Pettis (9/15/2009)
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.
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.
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
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.