Urgent:Transaction Rollback

Question

Urgent:Transaction Rollback

VastSQL

SSC-Dedicated

Points: 36069
More actions
August 12, 2018 at 10:43 pm

#376049

Hi Experts,
We have a procedure in database A which is inserting 10 millions records from a table in a database B to this database A and delete the same from database B.
We have stopped the job which calls this procedure and after the insert and now its in rollback stage and showing 12 hours to complete and deleting the data which it inserted.
Can i delete the procedure or take the database A offline so that the process will stop?

Viewing 9 posts - 1 through 8 (of 8 total)

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

VastSQL - Sunday, August 12, 2018 10:43 PM
Hi Experts,
We have a procedure in database A which is inserting 10 millions records from a table in a database B to this database A and delete the same from database B.
We have stopped the job which calls this procedure and after the insert and now its in rollback stage and showing 12 hours to complete and deleting the data which it inserted.
Can i delete the procedure or take the database A offline so that the process will stop?

I don't think that trying to stop the rollback is a good idea as the transactions are already marked for rollback and the best you could achieve would be data inconsistency.
😎
It would probably help to break down the work into smaller chunks, makes the operation more manageable.

VastSQL SSC-Dedicated Points: 36069 More actions · Answer 2

Eirikur Eiriksson - Sunday, August 12, 2018 11:22 PM
VastSQL - Sunday, August 12, 2018 10:43 PM
Hi Experts,
We have a procedure in database A which is inserting 10 millions records from a table in a database B to this database A and delete the same from database B.
We have stopped the job which calls this procedure and after the insert and now its in rollback stage and showing 12 hours to complete and deleting the data which it inserted.
Can i delete the procedure or take the database A offline so that the process will stop?
I don't think that trying to stop the rollback is a good idea as the transactions are already marked for rollback and the best you could achieve would be data inconsistency.
😎
It would probably help to break down the work into smaller chunks, makes the operation more manageable.

Thanks Eirikur. Can we modify the process which is already running? I am not bothered about the data in database A

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

VastSQL - Monday, August 13, 2018 12:16 AM
Eirikur Eiriksson - Sunday, August 12, 2018 11:22 PM
VastSQL - Sunday, August 12, 2018 10:43 PM
Hi Experts,
We have a procedure in database A which is inserting 10 millions records from a table in a database B to this database A and delete the same from database B.
We have stopped the job which calls this procedure and after the insert and now its in rollback stage and showing 12 hours to complete and deleting the data which it inserted.
Can i delete the procedure or take the database A offline so that the process will stop?
I don't think that trying to stop the rollback is a good idea as the transactions are already marked for rollback and the best you could achieve would be data inconsistency.
😎
It would probably help to break down the work into smaller chunks, makes the operation more manageable.
Thanks Eirikur. Can we modify the process which is already running? I am not bothered about the data in database A

That is unfortunately not an option, any attempts to modify a running process will result in an error, killing spids will not help either.
😎
What are the recovery models of the two databases, and if full or bulk, what is the t-log backup frequency?

VastSQL SSC-Dedicated Points: 36069 More actions · Answer 4

Eirikur Eiriksson - Monday, August 13, 2018 12:26 AM
VastSQL - Monday, August 13, 2018 12:16 AM
Eirikur Eiriksson - Sunday, August 12, 2018 11:22 PM
VastSQL - Sunday, August 12, 2018 10:43 PM
Hi Experts,
We have a procedure in database A which is inserting 10 millions records from a table in a database B to this database A and delete the same from database B.
We have stopped the job which calls this procedure and after the insert and now its in rollback stage and showing 12 hours to complete and deleting the data which it inserted.
Can i delete the procedure or take the database A offline so that the process will stop?
I don't think that trying to stop the rollback is a good idea as the transactions are already marked for rollback and the best you could achieve would be data inconsistency.
😎
It would probably help to break down the work into smaller chunks, makes the operation more manageable.
Thanks Eirikur. Can we modify the process which is already running? I am not bothered about the data in database A
That is unfortunately not an option, any attempts to modify a running process will result in an error, killing spids will not help either.
😎
What are the recovery models of the two databases, and if full or bulk, what is the t-log backup frequency?

Both Full and tlog backup frequency is 15 mins. Can you please help me understand what will happen if i drop the database A ?

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 5

VastSQL - Monday, August 13, 2018 1:03 AM
Eirikur Eiriksson - Monday, August 13, 2018 12:26 AM
VastSQL - Monday, August 13, 2018 12:16 AM
Eirikur Eiriksson - Sunday, August 12, 2018 11:22 PM
VastSQL - Sunday, August 12, 2018 10:43 PM
Hi Experts,
We have a procedure in database A which is inserting 10 millions records from a table in a database B to this database A and delete the same from database B.
We have stopped the job which calls this procedure and after the insert and now its in rollback stage and showing 12 hours to complete and deleting the data which it inserted.
Can i delete the procedure or take the database A offline so that the process will stop?
I don't think that trying to stop the rollback is a good idea as the transactions are already marked for rollback and the best you could achieve would be data inconsistency.
😎
It would probably help to break down the work into smaller chunks, makes the operation more manageable.
Thanks Eirikur. Can we modify the process which is already running? I am not bothered about the data in database A
That is unfortunately not an option, any attempts to modify a running process will result in an error, killing spids will not help either.
😎
What are the recovery models of the two databases, and if full or bulk, what is the t-log backup frequency?
Both Full and tlog backup frequency is 15 mins. Can you please help me understand what will happen if i drop the database A ?

Only one way to find out😉
😎
It should not have any effect on DB-B and therefore I think you should give it a try.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 6

My very strong recommendation would be to not screw with anything during the rollback. Been there, done dat.

--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)

VastSQL SSC-Dedicated Points: 36069 More actions · Answer 7

Eirikur Eiriksson - Monday, August 13, 2018 1:14 AM
VastSQL - Monday, August 13, 2018 1:03 AM
Eirikur Eiriksson - Monday, August 13, 2018 12:26 AM
VastSQL - Monday, August 13, 2018 12:16 AM
Eirikur Eiriksson - Sunday, August 12, 2018 11:22 PM
VastSQL - Sunday, August 12, 2018 10:43 PM
Hi Experts,
We have a procedure in database A which is inserting 10 millions records from a table in a database B to this database A and delete the same from database B.
We have stopped the job which calls this procedure and after the insert and now its in rollback stage and showing 12 hours to complete and deleting the data which it inserted.
Can i delete the procedure or take the database A offline so that the process will stop?
I don't think that trying to stop the rollback is a good idea as the transactions are already marked for rollback and the best you could achieve would be data inconsistency.
😎
It would probably help to break down the work into smaller chunks, makes the operation more manageable.
Thanks Eirikur. Can we modify the process which is already running? I am not bothered about the data in database A
That is unfortunately not an option, any attempts to modify a running process will result in an error, killing spids will not help either.
😎
What are the recovery models of the two databases, and if full or bulk, what is the t-log backup frequency?
Both Full and tlog backup frequency is 15 mins. Can you please help me understand what will happen if i drop the database A ?
Only one way to find out😉
😎
It should not have any effect on DB-B and therefore I think you should give it a try.

Thanks Eirikkur. The rollback completed but we had to wait a lot.

VastSQL SSC-Dedicated Points: 36069 More actions · Answer 8

Jeff Moden - Monday, August 13, 2018 6:09 AM
My very strong recommendation would be to not screw with anything during the rollback. Been there, done dat.

Thanks Jeff