5

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

enter image description here

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:

enter image description here

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.

enter image description here

question:

how can I stop spid54?

2
  • 1
    How critical is this database?...is it in production? Also, what version of SQL Server are you using?
    – J.D.
    Dec 20, 2023 at 12:35
  • the database is not critical, sqlwatch, but it sits on a critical server, and it is chocking the tempdb. I am trying to avoid restarting either services or server. there is replication and AG on this server. SQL Server 2016 (SP2-CU12) (KB4536648) Dec 20, 2023 at 12:38

1 Answer 1

9

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:

Accelerated Database Recovery process

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.

4
  • It'd be interesting to get a dump and see what the thread is trying to do. The wait type was HADR_CLUSAPI_CALL but none of those views seem as though they'd reach out to the cluster. If it's in that wait type, then it's preemptive, which is why it most likely isn't responding because it's not under the purview of a scheduler at the moment. Dec 20, 2023 at 16:03
  • @SeanGallardy Agreed. I noticed that wait type too and let Paul Randall explain what it meant to me since it's above my head lol. I can't imagine how inserting into a temp table would be related to anything waiting on HADR (presumably the Availability Group in use). But idk, my knowledge of AGs is limited too 🤷‍♂️.
    – J.D.
    Dec 20, 2023 at 16:28
  • 2
    @Sean that sqlwatch view joins to dm_hadr_database_replica_states which would explain the wait github.com/marcingminski/sqlwatch/blob/main/SqlWatch.Monitor/…
    – AMtwo
    Dec 21, 2023 at 5:26
  • Nice, I didn't know this was a open source thing, thansk @AMtwo! Dec 21, 2023 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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