Deconectarea sesiunilor se poate face manual, una cate una, ceea ce inseamna pierdere de timp, sau se poate face din script, alegand ca sesiunile mai vechi de x ore ( relative la coloana LastBatch) sa fie automat deconectate.

Pt acest lucru am conceput o procedura de sql. 

create procedure Kill_old_sessions_prc

@NrOre int

as

declare @spid int

declare @last_batch datetime

select

spid

,  substring( convert(varchar,last_batch,111) ,6  ,5 ) + ‘ ‘

+ substring( convert(varchar,last_batch,113) ,13 ,8 )

as ‘last_batch_char’

into #procese

from sys.sysprocesses

where spid>55 and cmd not like ‘%DB MIRROR%’ and status not like ‘%background%’ and loginame<>’sa’ and status like ‘%sleeping%’ and cmd like ‘%AWAITING COMMAND%’

alter table #procese alter column last_batch_char varchar(max)

update #procese

set last_batch_char = convert(varchar(max),YEAR(getdate()))+’/’+CONVERT(varchar(max),last_batch_char)

alter table #procese alter column last_batch_char datetime

declare @diferenta int

declare @sql varchar(max)

declare x cursor forward_only

for

select * from #procese

open x

fetch next from x into @spid, @last_batch

while @@FETCH_STATUS=0

begin

if DATEDIFF (HH, @last_batch, GETDATE()) > @NrOre

begin

set @sql = ‘kill ‘+ CONVERT(varchar(max),@spid)

exec (@sql)

print ‘am dat kill la spid ‘+ CONVERT(varchar(max),@spid)

end

fetch next from x into @spid, @last_batch

end

close x

deallocate x

drop table #procese

 Se pune aceasta procedura pe serverul pe care se doreste rulata (de preferat in baza de date Master, dar nu e obligatoriu sa fie aceasta baza).

Dupa care se ruleaza alegand ca parametru numarul maxim de ore pt o sesiune inactiva (de retinut ca se vor deconecta doar sesiunile INACTIVE)

Exemplu: Pt a deconecta sesiunile mai vechi de 48 de ore se executa: exec Kill_old_sessions_prc 48

In timpul rularii procedura va spune la ce sesiuni (SPID-ul) a dat disconect. (ca mai jos)

am dat kill la spid 56
am dat kill la spid 59
am dat kill la spid 60