解除sql server資料庫佔用

王明輝發表於2016-10-25
SQLServer資料庫

 

use master
go
declare @dbName nvarchar(50)
set @dbName='dbname' --資料庫名
declare @spid nvarchar(20) 
declare cur_lock cursor for 
SELECT DISTINCT request_session_id FROM master.sys.dm_tran_locks WHERE resource_type = 'DATABASE' AND resource_database_id = db_id(@dbName)
open cur_lock 
fetch cur_lock into @spid 
while @@fetch_status=0 
begin 
exec( 'kill '+@spid) 
fetch Next From cur_lock into @spid
end 
close cur_lock
deallocate cur_lock

 

use master

go

declare @dbName nvarchar(50)

set @dbName='dbname' --資料庫名

declare @spid nvarchar(20)

declare cur_lock cursor for

SELECT DISTINCT request_session_id FROM master.sys.dm_tran_locks WHERE resource_type = 'DATABASE' AND resource_database_id = db_id(@dbName)

open cur_lock

fetch cur_lock into @spid

while @@fetch_status=0

begin

exec( 'kill '+@spid)

fetch Next From cur_lock into @spid

end

close cur_lock

deallocate cur_lock

相關文章