Query stuck on 0% ROLLBACK for 2 days

Question

kill with statusonly returns:

transaction rollback in progress. Estimated rollback completion: 0%.

The query creates a temp table and runs on local server without using xp_cmdshell.

• CPU & reads are not changing.
• Lock info: wait_type = LCK_M_SCH_M
• wait_resource = OBJECT: 2:-1211568747:21 meaning its waiting for a temp table in tempdb.

Server info:

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: )

Is there any way to solve this without an instance restart?
Should I be expecting a database recovery?

sp_whoisactive shows its not blocked by anything. It's a temp in tempDB and its been 3 days now, I'm not sure something is going to happen.

Update: I ran beta_lockinfo (documentation here: beta_lockinfo) and it says the query is blocking itself, with the following info:

blklvl	blkby	cnt	object	rsctype	locktype	lstatus	ownertype	rscsubtype	waittime	waittype
DD	80	1	#cdrtable	OBJECT	Sch-M	WAIT	TRANSACTION	[21]	164116.577	LCK_M_SCH_M
DD	80	1	DBName	DATABASE	S	grant	STW	[21]
DD	80	1	#cdrtable	OBJECT	Sch-M	grant	TRANSACTION	[21]
DD	80	21	#cdrtable	OBJECT	Sch-M	grant	TRANSACTION	[0-20]
DD	80	1	ProcName	OBJECT	Sch-M	grant	TRANSACTION	[21]
DD	80	1	sys.sysobjvalues	KEY	S	grant	TRANSACTION
DD	80	1	sys.sysobjvalues	OBJECT	Sch-S	grant	TRANSACTION	[21]

Answer 1

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.