Stop rolling back transaction

Question

Stop rolling back transaction

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
January 29, 2019 at 5:18 pm

#376881

Hello, I want to delete records from multiple records (very old records and does not require anymore). I wrote sql procedure to cleanup the record. But if in case stop the transaction it is rolling back the delete which I want to prevent as I do not need those records anymore so rolling back is not my choice. Is there anyway through script I can avoid doing the rollback?
Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 1

SQL_Hunt - Tuesday, January 29, 2019 5:18 PM
Hello, I want to delete records from multiple records (very old records and does not require anymore). I wrote sql procedure to cleanup the record. But if in case stop the transaction it is rolling back the delete which I want to prevent as I do not need those records anymore so rolling back is not my choice. Is there anyway through script I can avoid doing the rollback?

Try doing DELETES in a loop. Each loop will be an atomic DELETE. Whatever DELETE that is executing will still be stopped and rolled back but it will be on a smaller number of rows and any previous rows deleted by other iterations of the loop will remain DELETEd.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

Jeff Moden - Tuesday, January 29, 2019 6:56 PM
SQL_Hunt - Tuesday, January 29, 2019 5:18 PM
Hello, I want to delete records from multiple records (very old records and does not require anymore). I wrote sql procedure to cleanup the record. But if in case stop the transaction it is rolling back the delete which I want to prevent as I do not need those records anymore so rolling back is not my choice. Is there anyway through script I can avoid doing the rollback?
Try doing DELETES in a loop. Each loop will be an atomic DELETE. Whatever DELETE that is executing will still be stopped and rolled back but it will be on a smaller number of rows and any previous rows deleted by other iterations of the loop will remain DELETEd.

Thanks, Jeff. I am using that approach actually. But each table itself is so bigger so wanted to know if there is anyway possible that while the first table is deleted but at the end if lets say some one accidentally kills the transaction or any other scenario it wont rollback the deletes. Can you suggest any other option or way out?

Thanks.

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 3

SQL_Hunt - Tuesday, January 29, 2019 7:26 PM
Jeff Moden - Tuesday, January 29, 2019 6:56 PM
SQL_Hunt - Tuesday, January 29, 2019 5:18 PM
Hello, I want to delete records from multiple records (very old records and does not require anymore). I wrote sql procedure to cleanup the record. But if in case stop the transaction it is rolling back the delete which I want to prevent as I do not need those records anymore so rolling back is not my choice. Is there anyway through script I can avoid doing the rollback?
Try doing DELETES in a loop. Each loop will be an atomic DELETE. Whatever DELETE that is executing will still be stopped and rolled back but it will be on a smaller number of rows and any previous rows deleted by other iterations of the loop will remain DELETEd.
Thanks, Jeff. I am using that approach actually. But each table itself is so bigger so wanted to know if there is anyway possible that while the first table is deleted but at the end if lets say some one accidentally kills the transaction or any other scenario it wont rollback the deletes. Can you suggest any other option or way out?

If the number of rows you want to delete represents a large enough proportion, you can write the rows you want to keep to a new table and simply truncate the existing table.
Outside of that, Jeff's suggestion is your best bet.
As far as being able to make a transaction "un-rollback-able"... Nope... That's kind of a core principle of the ACID model. A given transaction either succeeds 100% or fails 100%. There is no middle ground.