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?
navajo26354Asked:
Who is Participating?
 
VIVEKANANDHAN_PERIASAMYCommented:
You can restart the SQL server services in case you need immediate result.
0
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.