Link to home
Start Free TrialLog in
Avatar of fdereli
fdereli

asked on

sql rollback is taking forever HELP!!

I run a simple delete statement which run for 4 hr then I had to cancel it. Now it is rolling back but eating all system resources. What can i do to delay or kill entire roll back process without messing up something.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I agree with angelIII - you have to wait for the rollback to finish. If you try to kill the process SQL will probably ignore your request anyway.
Just a second opinion for you in case it sounds to horribl to be true.. The rollback is rolling back every portion of the delete you were doing. This is a very intensive process, and it takes just as much time (if not more usually) to roll something back.

I learned this the hard way once. Ever since then I batch my large deletes and updates into manageable chunks in case I need to rollback for some reason. I also "measure twice" before doing an update or a delete and I haven't had as many problems.
Go get some coffee. Then some lunch.. Then some Iced Coffee, then a mid afternoon snack. Play some pac-man and when you are done it will be done.
4 + hours  ....   I may be quicker to restart the sql server services ..  the db will be a mess  .. so you will have to restore the backup and the transaction logs (if you have them) to the point in time just before you ran the delete comand ... not best practice   :)    personaly I would just take the day off
>the db will be a mess
wrong. As the update was cancelled, it will be properly rolled back. guarantee by MS SQL Server
angelIII , as i stated , the db will be a mess .. if you restart the sql server while  rollback was in operation .. I wasnt saying the db would be a mess if the rollback completed successfully
Actually TAB8.. If he restarts the server while rollback is in operation the database would NOT be in a mess..

Read more about the ACID properties and how they are implemented by SQL Server, read about Transaction Logging, RECOVERY and what happens at startup...

If a database were to be that finnickey that everytime a restart/powerdown etc occurred while a Rollback operation was underway, we would be in a world of hurt in the SQL Community...

No, the restart will essentially still do the rollback, only now it would not be holding up a Query Analyzer session and would run as a background process as part of recovery.

Everytime SQL Server restarts the Transaction Log is read and any updates that had rolled back (or were rolling back) before the restart are then rolled back, any transactions that were committed but not written to disk are rolled forward essentially... Read about the Durability property especially...
TAB8: I recommend you to read books like "SQL Server Unleashed" resp. "Inside SQL Server", they explain this in detail.
when a transaction is rolled back, and sql server crashes in the middle of this rollback process, the rollback will be continued as service startup.
The database WILL NOT be in a mess. of course, if there are several statements that should be processes together but where NOT in a transaction, well, then the application code is the culprit, but the db will be technically fine.
Avatar of vijay4sql
vijay4sql

so finally.. how much time it took :-)
Avatar of fdereli

ASKER

Thanks you all for your responses Looks like there is no solution to my question.. I just let it finish and it took about 4 + hr to rollback. If i restarted the service sql server was going to continue to rollback. So first response gets all the credit.
Thanks
just a point of clarification.. why was his answer a B? It wasn't the answer you were hoping for, but it was dead on.