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

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.
0
fdereli
Asked:
fdereli
  • 4
  • 3
  • 2
  • +3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
nothing. rollback will probably use 4hours+
you could bounce the sql server instance, which at restart will do the rollback in the background, but still be intensive
0
 
ptjcbCommented:
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.
0
 
MikeWalshCommented:
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.
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
MikeWalshCommented:
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.
0
 
TAB8Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>the db will be a mess
wrong. As the update was cancelled, it will be properly rolled back. guarantee by MS SQL Server
0
 
TAB8Commented:
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
0
 
MikeWalshCommented:
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...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
0
 
vijay4sqlCommented:
so finally.. how much time it took :-)
0
 
fdereliAuthor Commented:
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
0
 
MikeWalshCommented:
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.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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