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

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 that you have read and understand our privacy policy and code of conduct.

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