All we need is an easy explanation of the problem, so here it is.
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.
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.
beta_lockinfo (documentation here: beta_lockinfo)
and it says the query is blocking itself, with the following info:
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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:
tempdbis 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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂