Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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.
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
BEGIN
DELETE TOP (@Batch)
FROM MyTable
WHERE Date < ‘20200401’';
IF @@ROWCOUNT < @Batch BREAK
END
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.
