We just had an interesting discussion among our team regarding the best way to remove a very large number of rows from a database without having the transaction log grow out of control. The scenario is something like this:
- Need to delete 50 billion rows from a table
- Need to do it when there is only 4 G of hard drive space on the server
- Cannot turn off logging for the entire database
At this point the only option that we've come up with is creating a stored procedure that deletes a range of records and then shrinks the database, as well as the transaction log. The execution of the stored procedure would delete approximately 250,000 records at a time.
Unfortuntely when posed with the question of how to best accomplish this feat this is all I (as well as others) could come up with. Is there a better way?