요샌 좀 어려운게 없어서 올리기 망설여지지만.. 그래도 ^^

 

이렇케 KILL 구문을 뽑아서 직접 KILL 시켜도 되구요

 

select 'kill ' + cast(spid as varchar(100)) from sysprocesses

where dbid = db_id('데이터베이스')

 

또는 아래와 같은 PROC를 Master 데이터베이스에 만들어두고 아주쉽게

exec Kill_All '데이터베이스명' 해도 됩니다. ^^

 

CREATE procedure Kill_All

@dbname varchar(50)

as

Begin

-- Declare all the relevant variables

declare @counter int,

@spid int

 

-- Temp table to store all the connections

Create table #StoreConnections

(

id int identity(1,1),

spid int,

kpid int,

dbid int

)

 

-- Insert all the connection SPIDs ...

Insert into #StoreConnections

Select spid,kpid,dbid from master..sysprocesses where kpid = 0 and dbid = db_id(@dbname) and spid > 40

 

Set @counter = 1

Set @spid = (Select spid from #StoreConnections where id = @counter)

 

-- Start killing all the SPIDs ...

While @spid <> ''

Begin

Select @spid

Exec ('Kill ' + @spid)

Set @counter = @counter + 1

Set @spid = (Select spid from #StoreConnections where id = @counter)

End

 

-- Cleanup activity.

Drop table #StoreConnections

 

End

 

이 게시물을...




profile

차주언
MSSQL DBA ,  MCT/ MCDBA
SQL프런티어 /