how to kill a rogue spid that is already killed?

5 votes

1 answer

1485 views

                          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?

Asked by Marcello Miorelli (17274 rep)

Dec 20, 2023, 12:28 PM
Last activity: Dec 20, 2023, 01:05 PM

how to kill a rogue spid that is already killed?

Related Questions