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'
Well, this seems to be lock due to parallel processing inside the tempdb.
You can try kill [processid] if you have the rights to?
Another way is to get more detailed process information with this:
SELECT * FROM sys.sysprocesses WHERE spid = YOURSPID
As the Process runs in DB:2 try this:
SELECT * FROM tempdb.sys.all_objects WHERE object_id = OBJECTID
As I've seen, you have edited your question. If the Spid is in KILLED/ROLLBACK you have to wait until your transaction is rolled back. After that the process will be killed and removed. You can't do anything else, as the transaction security must be given.