SQL Server Performance Forum – Threads Archive

killing a rolling back process

how do i kill a process that is rolling back without stopping SQL Server.

I’m afraid that is imposible. But not sure, so wait others oppinions.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I’ve treid this once and ended up with SQL Server restart. Determining how long the rollback will last depends on which release of SQL Server you’re running. Until SQL Server 2000, you didn’t have a reliable way to determine how long a connection in the rollback state would continue to run. When you cancel a query through Query Analyzer or simply close an application, your action kills the connection, putting the connection into a rollback state if a transaction was active. SQL Server 2000 adds a new WITH STATUSONLY option to the KILL command that lets you monitor the level of progress for a command that is in the rollback state. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Bear in mind that for large transactions, rollback can take longer than the transaction took originally. In my experience, patience in these situations is always a good thing. If you restart SQL Server, the database in question will go into recovery while the transaction is rolled back, so either way, you’ve got to wait. Better to do it gracefully. Tom Pullen
DBA, Oxfam GB

what if this is causing a lock on a table and the rollback wait time keeps increasing everytime you run kill with statusonly

also this is SQL Server service pack 3a

if the database goes into recovery for rollback after restarting SQL Server, no users will be able to get into it at all. but the rolling back process may be hung or stalled – knowing whether restarting is going to be a good thing or not is a black art! is it a live server? Tom Pullen
DBA, Oxfam GB

There is no difference in the ROLBACK process as compared to pre and post SP3a, this is defined by the SQL engine. I agree with Tom’s comments in considering the facts and figures. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

good thing is its not production its a dev. server, so i’ll just wait then… thanks guys just thought maybe there was something out there to stop the table lock going on at the moment due to the roll back on the database which is making an update impossible at the moment.

managed to stop the offending process by stopping the process from windows…thanks

]]>