Process/SPID is being blocked by itself, how to clear/kill without restarting Sql Server
We have a process that was running for 4 hours. Because it was running so long, it was causing other issues in the database, so it was decided to kill the process.
Now, the process is in a suspended state. It also states that it's being blocked by itself after querying sp_who2.
In activity monitor, here's the waitresource information:
objectlock lockPartition=0 objid=xxx subresource=FULL dbid=2 id=lockyyyy mode=X associatedObjectid=xxx
You'll notice that the objid and associatedObjectId are the same value.
Querying the sys.objects table shows NO results for that object id.
Is Sql Server waiting for a lock on an object that doesn't exist anymore? How can I get rid of this process without restarting Sql Server? (our DBA's are not responding to help requests).
Keep in mind, this is a test environment, but it is stopping all development/testing because we are unable to deploy any changes to our database, because one of those changes is affecting one of the objects that the process was accessing.
Edit: more info from activity monitor: Command = 'KILLED/ROLLBACK' TASK STATE = 'SUSPENDED'
Answer
I have experienced this may times. When you kill a large INSERT/UDPATE/DELETE statement, it can take hours to recover (if it ever does recover) from this state.
run kill <spid> with statusonly.
It will give you a percentage and estimated wait time of the ROLLBACK process.
Sometimes it says 0% or 100% and 0 estimated time. If you are patient, it may recover eventually. If you restart the server, the rollback process will be completed offline, and the database will show IN RECOVERY state and usually will be faster than waiting the server to recover itself.
Be aware that users won't be able to use the database until the recovery process ends, but if the SPID in KILLED/ROLLBACK state is locking other process, it might be an option to restart.