Menu
I have an uncommitted statement in perptual rollback mode in my database. When I try to kill the SPID associated with this statement, I get the following error:
transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
This uncommitted statement is causing users to not be able to view the DB's table, view, and procedure trees. How do I stop this SPID?
751
You cannot - if you were able to kill a rollback, it would leave your database in an inconsistent state. If you stop and start the SQL service, it will continue the rollback when it starts. You must simply wait for it to finish. No, because SQL finished the rollback after the server restarts.
Terminate running database backup using a KILL SPID command While the backup is in progress, execute the sp_who2 command to see the backup database process and note down the SPID for it. Let's assume we want to cancel the backup. Execute the KILL SPID command, and it immediately kills the backup process.
Killing a Blocking Process After you have connected, right click on the instance name and select 'Activity Monitor' from the menu. Once Activity Monitor has loaded, expand the 'Processes' section. Scroll down to the SPID of the process you would like to kill. Right click on that line and select 'Kill Process'.
I found this thread, it seems, if it is started you can't kill it with command. Only if you are making your db offline.
MSDN forum link
174
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
