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:
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/