Eu tenho um spid obstinado que não consigo matar e está impedindo que o log de transações do meu tempdb seja truncado
foi assim que encontrei esse aranha desonesto:
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
esta é a consulta que ele está executando (ou mantendo):
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)
está funcionando há mais de 60 horas:
Já foi morto.
então as coisas que tentei fazer:
alter database sqlwatch set single_user with rollback immediate
mas não funcionou
kill 54 with statusonly
SPID 54: reversão de transação em andamento. Conclusão estimada da reversão: 0%. Tempo restante estimado: 0 segundos.
pergunta:
como posso parar o spid54?
Infelizmente, não há muito que se possa fazer quando um processo começa a ser revertido, a não ser esperar. Todas as alterações feitas na transação (no seu caso, inserir vários registros em uma tabela temporária) precisam ser desfeitas. Ele também precisa readquirir a maioria dos mesmos bloqueios adquiridos anteriormente, para poder fazer a reversão. Se um bloqueio incompatível existente em uma das tabelas subjacentes estiver bloqueando isso, a reversão será interrompida até que o bloqueio seja resolvido. E a " melhor parte " é que as reversões acontecem em thread único e normalmente demoram mais do que todo o tempo já gasto para a consulta ser executada antes de ser encerrada.
A única outra opção normalmente é um encerramento não normal, como reiniciar o serviço SQL Server ou reiniciar a própria máquina do servidor. Isso corre o risco de perda e/ou corrupção de dados, e o banco de dados pode voltar a ficar on-line ainda em estado de recuperação. Infelizmente, isso não é aplicável no seu caso:
FWIW, a Microsoft melhorou muito o processo de recuperação e reversão no SQL Server 2019 e posterior com um recurso conhecido como Accelerated Database Recovery :
As etapas do processo de recuperação foram reestruturadas para que as reversões possam ocorrer quase imediatamente quando ativadas:
É um ótimo recurso que infelizmente também não se aplica ao seu caso aqui:
A única outra coisa em que consigo pensar é que você pode tentar colocar apenas esse banco de dados off-line, mas acredito que é improvável que funcione e aguardará bloqueado na transação que está sendo revertida.
Você também pode executar
sp_WhoIsActivepara ver se há mais alguma coisa em execução no servidor que esteja potencialmente bloqueando a reversão ou que esteja consumindo muito os recursos do servidor, o que também pode ser eliminado.sp_WhoIsActivedeve pelo menos informar se há progresso, vendo que o tempo de CPU e as leituras e gravações do comando de reversão continuam a aumentar.Infelizmente, você provavelmente está apenas esperando, o que pode levar vários dias.