use master go create proc killspid (@dbname varchar(20)) as begin declare @sql nvarchar(500) declare @spid int set @sql='declare getspid cursor for select spid from sysprocesses where dbid=db_id('''+@dbname+''')' exec (@sql) open getspid fetch next from getspid into @spid while @@fetch_status < >-1 begin exec('kill '+rtrim(@spid)) fetch next from getspid into @spid end close getspid deallocate getspid end --用法 use master exec killspid '数据库名'
可以是用事务处理太频繁,或是数据量太大可以用select spid from master..sysprocesses where dbid=db_id('databaseName') kill 查出来的spid值如果数据量太大,一定要对where后面的一些字段建立索引
go
create proc killspid (@dbname varchar(20))
as
begin
declare @sql nvarchar(500)
declare @spid int
set @sql='declare getspid cursor for
select spid from sysprocesses where dbid=db_id('''+@dbname+''')'
exec (@sql)
open getspid
fetch next from getspid into @spid
while @@fetch_status < >-1
begin
exec('kill '+rtrim(@spid))
fetch next from getspid into @spid
end
close getspid
deallocate getspid
end --用法
use master
exec killspid '数据库名'
kill 查出来的spid值如果数据量太大,一定要对where后面的一些字段建立索引