I have to Automate the Purging huge volume of data from main table to Archive.
Steps I am following :
I am inserting the data from main table to archive table.
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