I've been doing some research and I found references of self deadlocks that were bugs related to older versions like kb2963138 and KB4500574. Checking Latest updates for Microsoft SQL Server it seems your instance is not patched to the latest CU (released Jun 14 2022).

Your attempt to reproduce the error was not successful, so your query might not have been the cause of the problem alone.

I understand you asked for a solution where no restart would be required (unfortunately none was provided) and on the comments it was said a restart could not solve the problem, but I think in this specific case a restart could solve your problem since you mention the lock occurs on tempdb and it's the only database that is discarded and recreated every time the instance reboots, therefore no undo or redo phase should happen.

I wouldn't recommend you to restart your instance based only on this assumption, but since you now have a real reason to restart your server (apply recent patches) I'm positive to recommend it.

I've been doing some research and I found references of self deadlocks that were bugs related to older versions like kb2963138 and KB4500574. Checking Latest updates for Microsoft SQL Server it seems your instance is not patched to the latest CU (released Jun 14 2022).

Your attempt to reproduce the error was not successful, so your query might not have been the cause of the problem alone.

I understand you asked for a solution where no restart would be required (unfortunately none was provided) and on the comments it was said a restart could not solve the problem, but I think in this specific case a restart could solve your problem since you mention the lock occurs on tempdb and it's the only database that is discarded and recreated every time the instance reboots, as the doc says:

tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database.

Therefore, no undo or redo phase should happen to recreate your problem on the next boot of SQL Server.

I wouldn't recommend you to restart your instance based only on this assumption, but since you now have a real reason to restart your server (apply recent patches) I'm positive to recommend it.

I've been doing some research and the only reference to a self deadlock I could find was from a bug related to older versions. I don't know if it is common to stumble on an old bug that has been corrected on earlier versions, but it could be the case. Checking Latest updates for Microsoft SQL Server it seems your instance is not patched to the latest CU (released Jun 14 2022).

Your attempt to reproduce the error was not successful (assuming the SQL Server version was the same of production), so your query might not have been the cause of the problem.

I understand you asked for a solution where no restart would be required (unfortunately none was provided) and on the comments it was said a restart could not solve the problem, but I think in this specific case a restart could solve your problem since you mention the lock occurs on tempdb and it's the only database that is discarded and recreated every time the instance reboots, therefore no undo or redo phase should happen.

I wouldn't recommend you to restart your instance based only on this assumption, but since you now have a real reason to restart your server (apply recent patches) I'm positive to recommend it.

I've been doing some research and I found references of self deadlocks that were bugs related to older versions like kb2963138 and KB4500574. Checking Latest updates for Microsoft SQL Server it seems your instance is not patched to the latest CU (released Jun 14 2022).

Your attempt to reproduce the error was not successful, so your query might not have been the cause of the problem alone.

I understand you asked for a solution where no restart would be required (unfortunately none was provided) and on the comments it was said a restart could not solve the problem, but I think in this specific case a restart could solve your problem since you mention the lock occurs on tempdb and it's the only database that is discarded and recreated every time the instance reboots, therefore no undo or redo phase should happen.

I wouldn't recommend you to restart your instance based only on this assumption, but since you now have a real reason to restart your server (apply recent patches) I'm positive to recommend it.