5

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'

ganders
  • 7,285
  • 17
  • 66
  • 114

2 Answers2

2

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.

Mr Lister
  • 45,515
  • 15
  • 108
  • 150
MarianoC
  • 21
  • 2
1

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.

Ionic
  • 3,884
  • 1
  • 12
  • 33
  • thank you. Querying tempdb with your second query shows that it's a user table. Also, querying the sysprocesses table shows the same info as Activity Monitor....the SPID is in a suspended state, and the command = 'Killed/Rollback'. Now where to go from here? – ganders Jun 17 '15 at 13:25
  • Don't have permissions to 'Kill' – ganders Jun 17 '15 at 13:27
  • You have a column: waitresource. Maybe this isn't empty on this spid? Well if you haven't the option to kill it hopefully waitresource isn't empty. Otherwise your admin should woke up. :-) – Ionic Jun 17 '15 at 13:32
  • The waitresource column is in Activity Monitor. Not sure what you mean by that comment. (Keeping my fingers crossed that the DBA's will help...eventually) – ganders Jun 17 '15 at 13:35
  • I've edited my answer. Due to your latest changes in your question (KILLED/ROLLBACK) – Ionic Jun 17 '15 at 13:47
  • K, sounds like I'm dead in the water. – ganders Jun 17 '15 at 13:49
  • Yes a bit. And it depends on the running time. For example if your procedure runs 2 hours and permanently inserted values, it could be that your rollback will run 2 hours too. – Ionic Jun 17 '15 at 14:10
  • process normally takes 5 seconds to 1 minute. It was running for 3 hours and 15 minutes, so the DBA's [tried to] kill it. Has been sitting in the killed/rollback state for 20 hours now. DBA's responded. They are going to try recycling Sql Server. If that doesn't work, restore the database. – ganders Jun 17 '15 at 14:13