Process / SPID is blocked by itself, how to clean / kill without restarting Sql Server
We have a process that runs for 4 hours. Since it ran for so long, it was causing other problems in the database, so it was decided to kill the process.
The process is now in a suspended state. It also claims that it blocks on its own after sp_who2 is requested.
In the activity monitor, here's the wait state information:
objectlock lockPartition=0 objid=xxx subresource=FULL dbid=2 id=lockyyyy mode=X associatedObjectid=xxx
You will notice that objid and associatedObjectId have the same meaning.
The query for the sys.objects table has NO results for this object identifier.
Is Sql Server waiting for a lock on an object that no longer exists? How can I get rid of this process without restarting Sql Server? (our DBA does not respond to help requests).
Keep in mind that this is a test environment, but it stops all development / testing because we cannot deploy any changes to our database because one of these changes affects one of the objects the process was accessing.
Edit: More info from the activity monitor: Command = 'KILLED / ROLLBACK' TASK STATE = 'SUSPENDED'
source to share
Well it looks like blocking due to parallel processing inside tempdb.
Can you try kill [processid]
if you have rights to?
Another way is to get more detailed information about the process with this:
SELECT * FROM sys.sysprocesses WHERE spid = YOURSPID
When the process starts in DB: 2 try this:
SELECT * FROM tempdb.sys.all_objects WHERE object_id = OBJECTID
As I already saw, you edited your question. If Spid is in KILLED / ROLLBACK then you need to wait for your transaction to roll back. After that, the process will be killed and deleted. There is nothing you can do as the transaction must be secure.
source to share
I have experienced this at times. When you delete a large INSERT / UDPATE / DELETE statement it can take hours to recover (if it ever recovers) from this state.
run kill <spid> with statusonly.
It will give you the percentage and the expected wait time for the ROLLBACK process.
Sometimes he says 0% or 100% and 0 is the estimated time. If you're patient, it may eventually recover. If you restart the server, the rollback process will be completed offline and the database will show an IN RECOVERY status and will generally be faster than waiting for the server to recover.
Be aware that users will not be able to use the database until the restore process is complete, but if a SPID in the KILLED / ROLLBACK state is blocking another process, it might be an option to restart.
source to share