August 26, 2016 at 3:16 am
Hi All,
I have 20 tables to be archived. Am moving them to archive database from production and deleting from production. They have up to 3 billion rows.
Am using transaction and while loop with @@rowcount. Sample code would be like this.
WHILE 1 = 1
BEGIN
BEGIN TRANSACTION
DELETE FROM A WHERE...
DELETE FROM B WHERE...
DELETE FROM C WHERE...
IF @@ROWCOUNT = 0 BREAK
COMMIT
END
Since am doing in a hierarchy (because am using ID) and table C is comparatively small, @@ROWCOUNT returns 0 after first execution the WHILE loop ends. How to run it effectively? Is there any alternate way to do this in a more feasible way?
______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂
August 26, 2016 at 11:51 pm
If you can, in 3 loops (preferably with each loop in its own UN-nested transaction, preferably using DELETE TOP, and preferably with a waitfor delay in each loop) delete all grandchildren first (A), then delete all children (B), and finally delete all parents (A). But, it would be much faster (and with far less logging) to implement a sliding window partition.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply