我有一个无法杀死的顽固 spid,它阻止了我的 tempdb的事务日志被截断
这就是我发现这个流氓 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
这是它正在运行(或保留)的查询:
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)
它已经运行了60 多个小时:
它已经被杀死了。
所以我尝试做的事情:
alter database sqlwatch set single_user with rollback immediate
但没用
kill 54 with statusonly
SPID 54:正在进行事务回滚。预计回滚完成:0%。预计剩余时间:0 秒。
问题:
我怎样才能停止spid54?
不幸的是,一旦进程开始回滚,除了等待之外,我们无能为力。事务所做的所有更改(在您的情况下将一堆记录插入临时表中)都需要撤消。它还需要重新获取之前获取的大部分相同锁,以便进行回滚。如果基础表之一上现有的不兼容锁阻止了这一操作,则回滚将挂起,直到该阻止解决为止。“最好的部分”是回滚是单线程发生的,并且通常需要比查询被终止之前运行的所有时间更长的时间。
唯一的其他选项通常是非正常终止,例如重新启动 SQL Server 服务或重新启动服务器计算机本身。这存在数据丢失和/或损坏的风险,并且数据库可能仍以恢复状态重新上线。不幸的是,这不适用于您的情况:
FWIW,微软通过一项称为加速数据库恢复的功能极大地改进了 SQL Server 2019 及更高版本中的恢复和回滚过程:
恢复过程步骤进行了重组,以便在启用后几乎可以立即进行回滚:
这是一个很棒的功能,不幸的是它也不适用于您的情况:
我唯一能想到的另一件事是,您可以尝试使该数据库脱机,但我相信它不太可能起作用,并且将等待锁定当前回滚的事务。
您还可以运行
sp_WhoIsActive
以查看服务器上当前正在执行的其他内容是否有可能阻止回滚或大量占用服务器资源(也可能是可杀死的)。sp_WhoIsActive
至少应该通过查看回滚命令的 CPU 时间和读写次数继续增加来告诉您是否有进展。不幸的是,您可能只是等待,这可能需要几天的时间。