Post reply

How to stop a long rollback?

  • June 14, 2007 at 3:53 pm

    #713067

    i think detaching will cause the same issues as dropping ... ie) it may not let you if it's processing a rollback. But if it does let you take it offline, or detach, then you could can the db altogether and do a fresh restore from a backup (if business says this is acceptable).

    i also forgot.. see if it will let you set the transaction log maximum file size (change it from unlimited). This helped me when i almost repeated the same thing.. it just cancelled the query saying there weren't enough log resources to complete the task.

  • June 14, 2007 at 4:22 pm

    #713070

    There really isn't any sort of precedence for T-SQL commands.  The precedence is done in the locking.  Your 'DROP TABLE' command is being blocked by the locks that the ROLLBACK statement is placing on the object. 

    As far as your infinite loop / INSERT test, I would say that if you were to take a real close look at what is happening in terms of locking, you would see that each interation of the loop is briefly locking the clustered index on the table and then releasing the lock immediately after the INSERT.  This allows the DROP TABLE statement to sneak into the locking queue and drop the table between interations of your loop.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • June 15, 2007 at 3:39 pm

    #713414

    uofmoo,

    Did you set the limit for the transaction log  growth during the execution of the query which you wanted to cancel or during the rollback operation of the query you cancelled?

     

    Thanks

  • June 15, 2007 at 3:40 pm

    #713415

    John,

    Thanks for the info ... it is helpful!

  • June 16, 2007 at 7:53 am

    #713456

    But just keep in mind if you restart the SQL Server services the database will go in a recovery mode...

     

  • June 18, 2007 at 9:20 am

    #713583

    I had the same problem a while ago. I was lucky that my first step was to backup the database. I stopped SQL, deleted the MDF and LDF files, restarted SQL and then restored from my backup, and then re-ran my updates (after dropping the indexes that were killing my update query). But ugly way to go ...

  • June 18, 2007 at 2:58 pm

    #713663

    A restart might clear it, but it could also just begin the recovery / rollback process right back up, or worse case scenario make your database suspect.

    The rollback operation typically takes just as long as the process took to get that far. If it is just a development database and you want to restore a backup and just free up the system resources you could stop the service, manually delete the mdf / ldf and restart the service. Then restore.

    If it is production and it is not crippling your box I would just let it finish.

  • June 18, 2007 at 4:26 pm

    #713676

    Thanks for the info everyone !!!

  • February 27, 2009 at 11:34 am

    #951322

    Reinstalling SQL Server is also an option.

  • February 27, 2009 at 11:44 am

    #951326

    seizorek (2/27/2009)

    Reinstalling SQL Server is also an option.

    Huh? Reinstalling SQL Server is an option for a process which is taking a long time to roll back?

  • September 1, 2010 at 11:02 am

    #1215164

    You could stop the rollback using a magnet... definitely an option

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply