Process in suspended state when doing a rollback


Microsoft SQL server 2016


A rollback of a deadlock is hung on itself. You have try to kill a process that was hung, but not it is stuck in suspended state.

Suggested solution:

Go to SSMS – Activity Monitor – sort on the Command column to find the rollback processes. Note down the ID

Check if this process is doing anything with this command:

select percent_complete, * from sys.dm_exec_requests where session_id = 69  -- change to your id

If the values does not change from 0%, then the process is most likely not doing anything.

From the result of above statement, you can of wait_resource column find out what table is creating the lock.

wait_resource = “KEY: 40:844424931901440 (7210abc) ” =  Database_Id, HOBT_Id

The first number is the database – use this SQL query to find what:

SELECT     name    FROM    sys.databases    WHERE      database_id=40;

The second number is the table that gives the issue – use this SQL query in the database to find where:

SELECT as schema_name, as object_name, as index_name

FROM sys.partitions AS p

JOIN sys.objects as so on 


JOIN sys.indexes as si on 

    p.index_id=si.index_id and 


JOIN sys.schemas AS sc on 


WHERE hobt_id = 844424931901440;


If you can not find the other blocking process and stop it, and a recreate of a index does not help, then your option is to restart the SQL server service in Microsoft Windows.

Obtain some downtime on the SQL server, and restart the service in hope it will solve the deadlock.

More information: