Welsh Corgi (3/3/2013)
GilaMonster (3/3/2013)
Welsh Corgi (3/3/2013)
GilaMonster (3/3/2013)
Welsh Corgi (3/3/2013)
Most times I've seen that it's been a process that has some external component. DTC, remote procedure call, external access, extended procedure, backup. Killing a normal SQL process you shouldn't have any problems. Just don't do anything silly like restarting SQL part way through a rollback or deleting the transaction log.Yes but what do you do when the rollback does not make any process 0% of 0% completed?
The vast majority times I've seen that, it's been one of the above cases and it's something outside of SQL that's 'stuck', there's no actual rollback to do and hence it can be ignored or SQL can be restarted, that's the 0%, 0 seconds remaining scenario.
When you have 0% and a non-0 seconds, you wait for the rollback to finish, checking that it's not waiting for anything that you can fix.
In this case it was a Stored Procedure being blocked by T-SQL Code.
When I get the 0% there is nothing that I can do.
I would expect that it would not be stuck at 0%?
If it's 0% complete and a non-0 seconds remaining, you wait. Rollbacks take longer than the time to make the original changes, so if you're rolling back something that took 3 hours, it'll take longer than 3 hours to roll back. Just check that the rollback is not blocked or waiting for something you can fix.
I wouldn't kill the procedure in that case, I'd either kill the T-SQL that was causing the blocking or I'd get the person running it to stop the query (which amounts to the same thing)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability