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?
Aneesh
check for any conflicting blocks by running sp_Who2 and kill if any. Otherwise you need to wait
It will take the same amount of time to rollback as it did to insert all the rows.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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>
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>
<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!!!
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!!!
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.
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.
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!!!
Thanks for your advice acperkins!!!
Thanks for your advice acperkins!!!
No problem, it is free.
No problem, it is free.