MSSQL Large Delete

Solution 1:

I have had the best success when doing similar tasks with the following flow:

1. Copy the data to keep into a temporary table
2. Truncate the original table to purge all data
3. Move everything from the temporary table back into the original table

One major benefit of this is that your indexes will be rebuilt as you put the data back into the original table.

Solution 2:

Well, if you were using SQL Server Partitioning, say based on the date column, you would have possibly switched out the partitions that are no longer required. A consideration for a future implementation perhaps.

I think your only option may be to delete the data in smaller batches, rather than in one hit, so as to avoid any potential blocking issues.