2

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]
4
  • Instance restart will not solve the issue as active transactions will be recommited or rollbacked again at start.
    – MBuschi
    Jul 20 at 8:48
  • Could you create a test database and run the problematic query to see if it causes the same issue?
    – Ronaldo
    Jul 20 at 12:43
  • Yes I will recreate this on a local server and update
    – Kati
    Jul 20 at 15:31
  • @Ronaldo I have tried reproducing but was unable. The query just stopped (after pressing stop button) and I couldnt even reach a state that I had to use KILL. But its a backup server and it was the only proccess. Any ideas?
    – Kati
    Jul 21 at 9:33

1 Answer 1

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.

1
  • 1
    Thank you for your help. Eventually we did a restart and of course the query was gone. I am still very curious about how this happened and why. I should definitely patch the SQL server!
    – Kati
    Jul 27 at 8:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.