Deleting large amounts of data
I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes the transaction log to grow.
Doesn’t matter what recovery model you are using, the transaction log will grow.
So if they went down the traditional delete path you are looking at lots of batch deletes, over a long period of time.
An alternative could be to
Copy the data you want to keep to a staging table e.g. Manifest_Stage
Truncate the Manifest table – truncate doesn’t use the transaction log as much
Import the data from Manifest_Stage to Manifest.
Drop the staging table
You could also just do the first step and then drop Manifest and then rename Manifest_Stage to Manifest.
This is of course a simplified overview as you would need to also manage Primary Key and Foreign Key relationships if they exist. Then make sure that the indexing is correct and any other table constraints or triggers etc.