Problems clearing connections

[DebiJo]

We have an application that seems to be having a hard time clearing connections. The vendor has acknowledged the problem and says it will corrected in the next release (mid-year). In the meantime, I have a mess. We can have as little as 40 people logged on to the application and we'll have upwards of 350 connections. After we get that many connections, users that are legitimately logged in start getting time outs and connection problems.

Yesterday, we kicked everyone out (still showed over 200 connections) then I went into SQL, detach database and clicked clear connections (I did not detach). Then we let everyone back in and that solved our problem. Of course give it a couple of days and we'll be stuck again.

My question is, is there a way I can script the connection clearing? So we can run this at night when no one is in the db? I can't stop the whole server cause other db's are in it working. Any ideas?

Thanks in advance,
Debi

 

[nigelrivett]

Have a look at sysprocesses to see what the hostname is - or any other method of identifying the app and try the below.
I would bounce the server instead though - maybe a couple of times a day.


declare @spid intm @cmd varchar(1000)
select @spid = 10
while exists (select * from sysprocesses where hostname = 'myapp' and spid > @spid
begin
select @spid = min(spid) from sysprocesses where hostname = 'myapp' and spid > @spid)
select @cmd = 'kill ' + convert(varchar(20),@spid)
exec (@cmd)
end


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

Actually I wouldn't use this vendors software.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[gcole]

How do you 'bounce the server'? I am also stuck with a vendor software that is poorly written.

 

[nigelrivett]

Bounce, as in stop the sql server service then start it again.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.