• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1091
  • Last Modified:

Rollback taking a long time

I ran an insert statement which inserted records from one table into a table that had zero records in it.  After 10 hours the number of records inserted was less than half of the total  number of records  to be inserted.  I killed the insert process and now it is in rollback status.  It is slowly removing the inserted records and will continue to do so until the table goes back to zero records.  I have the database in simple mode.  I don't understand why it has to rollback one record at a time.  Normally when I stop an insert process it is immediate.  I don't have 10 hours to wait for this rollback to finish.  Is there something I can do to stop this and to avoid this problem in the future?
0
navajo26354
Asked:
navajo26354
  • 4
  • 3
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
check for any conflicting blocks by running sp_Who2 and kill if any. Otherwise you need to wait
0
 
Anthony PerkinsCommented:
It will take the same amount of time to rollback as it did to insert all the rows.
0
 
VIVEKANANDHAN_PERIASAMYCommented:
You can restart the SQL server services in case you need immediate result.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Anthony PerkinsCommented:
You can restart the SQL server services in case you need immediate result.
As you have no doubt discovered that is totally useless and potentially dangerous.  If you do not corrupt your database it will continue to rollback if needed after you restart the service.

<offtopic>
It is really a shame that members here are prepared to offer advice that is not only not helpful, but can be downright dangerous.
</offtopic>
0
 
VIVEKANANDHAN_PERIASAMYCommented:
<offtopic>
It is really a shame that members here are prepared to offer advice that is not only not helpful, but can be downright dangerous.
</offtopic>

Since user wants to rollback the changes immediately,and cannot wait for long time,I have provided the solution what user want and also let him to take his own decision to do it or not.
So i doesn't see any shameless here.Thanks for your comment!!!
0
 
Anthony PerkinsCommented:
Since user wants to rollback the changes immediately,and cannot wait for long time,I have provided the solution
Let's follow that thought through.  Why do you think that the rollback will be any faster by shutting down the service?  Have you ever done it?  By the same token and since you obviously like living dangerously, why don't you tell them to unplug the server?  That will certainly stop the rollback in its tracks!

Just remember that our position here as members offering advice is much like a doctor:  "Do no harm".  If you feel your advice to shut down the service "does no harm", I have nothing further to say to you, other than to wish you (and anyone who follows that advice) good luck and hope that you have your resume updated.
0
 
VIVEKANANDHAN_PERIASAMYCommented:
Why do you think that the rollback will be any faster by shutting down the service?  Have you ever done it? yes, i have done it before.I have experienced this before.

Thanks for your advice acperkins!!!
0
 
Anthony PerkinsCommented:
Thanks for your advice acperkins!!!
No problem, it is free.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now