SQLServer 查询当前数据库连接并清除未关闭的连接

2019-08-08

SQL Server

SQLServer 查询当前数据库连接并清除未关闭的连接

--查询数据库实例的所有连接
select * from master.dbo.sysprocesses where dbid = DB_ID('DBName')

--清除指定实例的所有数据库连接
USE master  
go   
 
declare   @programName     nvarchar(200),   
@spid   nvarchar(20)   
   
declare   cDblogin   cursor   for  
select   cast(spid   as   varchar(20))  AS spid   from   master..sysprocesses   where   dbid=db_id('xgj_w1_demo')   
open   cDblogin  
fetch   next   from   cDblogin   into   @spid   
while   @@fetch_status=0   
begin     
--防止自己终止自己的进程    
--否则会报错不能用KILL 来终止您自己的进程   
IF  @spid <> @@SPID  
    exec( 'kill   '+@spid)   
fetch   next   from  cDblogin   into   @spid   
end      
close   cDblogin   
deallocate   cDblogin

Related Posts