How to clear connections in Sql Server 2005

Question

At my workplace there are sellers using a third-party desktop application that directly connects the Sql server, and the software leaves hundreds of sleeping connections for each user. Is there a way to clear this connection programmatically?

Answer 1

What version of SQL Server are you running? You can write a stored procedure for this by looking at the data from sp_who, and then guess a little about the last action. There, the "LastBatch" column, which does the last time something, was submitted by this user. I would say if it is more than an hour (or any other), do KILL for this SPID.

SQL 2005 :

declare @spid int
   , @cmd varchar(200)
declare Mycurs cursor for
select spid
 from master..sysprocesses
 where status = 'sleeping'
 and last_batch > dateadd( s, -1, getdate())
open mycurs
fetch next from mycurs into @spid
while @@fetch_status = 0
 begin
  select @cmd = 'kill ' + cast(@spid as varchar)
  exec(@cmd )
  fetch next from mycurs into @spid
 end
deallocate MyCurs

Answer 2

, , ?

- - (.. webservice. , , , connection.close())

sqserver mgmt studio, sp_who2, , , - , 1 . , 100, db. , , .

. , asp.net Sqlserver . ""? , - ?