How to stop a long rollback?

Question

How to stop a long rollback?

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

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

uofmoo SSC Veteran Points: 204 More actions · Answer 1

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.

John Rowan SSC Guru Points: 56440 More actions · Answer 2

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

sql_er SSCarpal Tunnel Points: 4135 More actions · Answer 3

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

sql_er SSCarpal Tunnel Points: 4135 More actions · Answer 4

sql_er

SSCarpal Tunnel

Points: 4135

June 15, 2007 at 3:40 pm

#713415

John,

Thanks for the info ... it is helpful!

U J SSCrazy Points: 2423 More actions · Answer 5

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

Simon Facer SSCertifiable Points: 5427 More actions · Answer 6

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 ...

Joel Ewald SSCertifiable Points: 6120 More actions · Answer 7

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.