I have to Automate the Purging huge volume of data from main table to Archive.

Steps I am following :

  1. I am inserting the data from main table to archive table.

  2. After cross verifying the count in both tables , deleting the data from main table.

Risk: Delete statement may go in hung state and DB will go in recovery mode.

Note: we can't perform any DDL operation on main table as it's live transaction table. We can go with DML.

  • Database: SQL Server 2016
  • OS: Windows and IIS
  • switching table partitions is the most efficient way to move data from one table to another, it's almost instantaneous. cathrinewilhelmsen.net/2015/04/19/… Commented Aug 15, 2020 at 9:02
  • In fact partitioning might eliminate the need for another table altogether. Commented Aug 15, 2020 at 13:32

1 Answer 1


DDL is one of the main ways of speeding up the deletion of records, but seeing that DDL is not an option and you're facing 'hung states' for delete statements, perhaps I suggest looking into deleting records in batches?

Please see link, and here's a query example based on link:

DECLARE @Batch INT = 1000
WHILE 1 = 1
    DELETE TOP (@Batch)
    FROM MyTable
    WHERE Date < ‘20200401’';
    IF @@ROWCOUNT < @Batch BREAK

In this case, when we're deleting by Date, make sure you've got an index on Date so that SQL Server can quickly grab just those rows. You'll want to avoid table-level locking.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.