Process in suspended state when doing a rollback

Product:

Microsoft SQL server 2016

Issue:

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 

    sc.name as schema_name, 

    so.name as object_name, 

    si.name as index_name

FROM sys.partitions AS p

JOIN sys.objects as so on 

    p.object_id=so.object_id

JOIN sys.indexes as si on 

    p.index_id=si.index_id and 

    p.object_id=si.object_id

JOIN sys.schemas AS sc on 

    so.schema_id=sc.schema_id

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.

https://www.mssqltips.com/sqlservertip/6307/how-to-stop-and-start-sql-server-services/

More information:

LCK_M_S

https://www.brentozar.com/archive/2014/03/happens-issue-kill/

https://linuxhint.com/sql-server-kill-spid/

https://littlekendra.com/2016/10/17/decoding-key-and-page-waitresource-for-deadlocks-and-blocking/