how to kill a rogue spid that is already killed?

Question

I have an obstinate spid that I cannot kill and it is preventing the transaction log of my tempdb to get truncated

this is how I found this rogue spid:

if object_id('tempdb..#OpenTranStatus','U') is not null
   drop table #OpenTranStatus

CREATE TABLE #OpenTranStatus (
ActiveTransaction varchar(25),
Details sql_variant
);
 
-- Execute the command, putting the results in the table.
INSERT INTO #OpenTranStatus
EXEC ('DBCC OPENTRAN (sqlwatch) with tableresults')
SELECT * FROM #OpenTranStatus

this is the query it is running (or holding on to):

select d.database_id , sd.sqlwatch_database_id, sd.sql_instance
        into #d
        from dbo.vw_sqlwatch_sys_databases d

        inner join [dbo].[sqlwatch_meta_database] sd 
            on sd.[database_name] = d.[name] collate database_default
            and sd.[database_create_date] = case when d.name = 'tempdb' then '1970-01-01 00:00:00.000' else d.[create_date] end
            and sd.sql_instance = @sql_instance

        left join [dbo].[sqlwatch_config_exclude_database] ed
            on d.[name] like ed.database_name_pattern collate database_default
            and ed.snapshot_type_id = @snapshot_type_id
        where ed.snapshot_type_id is null
        option (keep plan)

it has been running for over 60 hours:

It has already been killed.

so the things I have tried doing:

alter database sqlwatch set single_user with rollback immediate

but it did not work

kill 54 with statusonly

SPID 54: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

question:

how can I stop spid54?

Answer 1

SPID 54: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

Unfortunately there's not much one can do once a process begins rolling back other than wait. All the changes that the transaction made (in your case inserting a bunch of records into a temp table) need to be undone. It also needs to re-acquire most of the same locks it previously acquired, in order to do the rollback. If an existing incompatible lock on one of the underlying tables is blocking this, then the rollback will hang until that blocking resolves. And the "best part" is rollbacks happen single-threaded, and typically take longer than all the time that was already spent for the query to run before it was killed.

The only other option typically is a non-graceful termination such as restarting the SQL Server service or restarting the server machine itself. This comes at the risk of data loss and / or corruption, and the database may come back online in a Recovering state still. Unfortunately that's not applicable in your case:

it sits on a critical server...I am trying to avoid restarting either services or server.

FWIW, Microsoft greatly improved the recovery and rollback process in SQL Server 2019 and later with a feature known as Accelerated Database Recovery:

Accelerated database recovery (ADR) improves database availability, especially in the presence of long running transactions, by redesigning the SQL database engine recovery process.

At a high level, ADR achieves fast database recovery by versioning all physical database modifications and only undoing logical operations, which are limited and can be undone almost instantly. Any transactions that were active at the time of a crash are marked as aborted and, therefore, any versions generated by these transactions can be ignored by concurrent user queries.

The recovery process steps were restructured such that rollbacks can happen almost immediately when it's enabled:

It's a great feature that unfortunately doesn't apply in your case here either:

SQL Server 2016

The only other thing I can think of is you can try taking just that database offline, but I believe it's unlikely to work and will wait locked on the transaction currently rolling back.

You can also run sp_WhoIsActive to see if there's anything else currently executing on the server that's potentially blocking the rollback or that's heavily using up server resources, that might be killable as well. sp_WhoIsActive should at least tell you if there's progress occurring by seeing that the CPU time and the Reads and Writes for the rollback command continue to increase.

Unfortunately you're probably just stuck waiting which may take multiple days.