sql - 进程 ID 'x' 不是事件进程 ID

Question

我需要每1小时将事务日志文件恢复到处于备用模式的数据库DBrestore中。因此，我创建了一个作业，它首先杀死所有进程，然后将日志文件恢复到数据库中，但有时作业会失败，并给出错误:进程 id '71' 不是事件进程 ID，进程 id 每次都会更改它失败。我使用此查询来终止进程

declare @sql as varchar(20), @spid as int
select @spid = min(spid)  from master..sysprocesses  
 where dbid = db_id('DBrestore') 
 and spid != @@spid    

while (@spid is not null)
begin
print 'Killing process ' + cast(@spid as varchar) + ' ...'
set @sql = 'kill ' + cast(@spid as varchar)
exec (@sql)

select 
    @spid = min(spid)  
from 
    master..sysprocesses  
where 
    dbid = db_id('DBrestore') 
    and spid != @@spid
end 

我需要对此查询进行哪些更改，以确保它只获取处于事件状态的进程 ID。

提前致谢

Answer 1

在下面链接提供的文档中，它提到“事件”进程是通过状态捕获的。如果您将其添加到 where 条件，它应该将您的查询限制为事件进程。

declare @sql as varchar(20), @spid as int
select @spid = min(spid)  from master..sysprocesses  
 where dbid = db_id('DBrestore') 
 and spid != @@spid    

while (@spid is not null)
begin
print 'Killing process ' + cast(@spid as varchar) + ' ...'
set @sql = 'kill ' + cast(@spid as varchar)
exec (@sql)

select 
    @spid = min(spid)  
from 
    master..sysprocesses  
where 
    dbid = db_id('DBrestore') 
    and spid != @@spid
    and status = 'running'
end 

https://learn.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysprocesses-transact-sql