Is there a T-SQL statement to clear the database connection ??? Thank you

tzdt9v

Sometimes T-SQL statements are used to detach the database, but it is unsuccessful because there are connections in use. Is there a T-SQL statement to clear the database connection ???

hiqwolfzzh

alter database [database name] set offline with rollback after 10

kxiaper

- Upstairs is offline
- Suppose you want to clear the database abc connection
declare @spid int
declare @strsql varchar(256)
select @spid=min(spid) from master.dbo.sysprocesses
while @spid is not null
begin
  set @strsql='kill '+ cast(@spid as varchar)
  print(@strsql)
  select @spid=min(spid) from master.dbo.sysprocesses
  where spid>@spid and db_name(dbid) ='abc' and dbid<>0
end
go

hiqwolfzzh

alter database [database name] set offline with rollback after 10


You can check the online help'alter database'
^_^

unkmass

Use kill to kill all connection processes

hiqwolfzzh

Oh like this

USE Master
  SET NOCOUNT ON
  Declare @spID varchar(100)
  Declare @SQL varchar(5000)
  Select @spID=``,@SQL=''
   
  Create table #tmp (spid int,ecid int,status varchar(50),loginame varchar(50),hostname varchar(100),blk int,dbname varchar(50),cmd varchar(400))
   
  Insert into #tmp
  Exec sp_who
   
  Select @SQL=@SQL+' Kill'+Cast(spid as varchar) From #tmp where dbname='Northwind'
  exec (@SQL)
  Drop table #tmp

wgfxman

KILL!

tzdt9v

OK, thank you
I used a relatively simple alter database [database name] set offline with rollback after 10