Situatie
Avem nevoie uneori, in special cand vrem sa restauram o baza de date, sa deconectam rapid (fara sa luam fiecare sesiune pe rand) toate sesiunile active pe acea baza de date.
Pentru aceasta am facut o procedura stocata ce ia ca parametru numele unei baze de pe server si deconecteaza toate sesiunile de pe acea baza.
Solutie
create procedure [dbo].[BD_Kill_all_sessions_on_a_database_prc]
@database varchar(100)
as
declare @spid int
declare @sql varchar(100)
declare spid cursor forward_only
for
select spid
from master.dbo.sysprocesses sp
join master.dbo.sysdatabases sd on sp.dbid=sd.dbid
where name= @database
order by 1
open spid
fetch next from spid into @spid
while @@fetch_status=0
begin
set @sql = ‘kill ‘+CONVERT(VARCHAR(3),@spid)
exec (@sql)
fetch next from spid into @spid
end
close spid
deallocate spid
Leave A Comment?