My workplace has sales people using a 3rd party desktop application that connects directly the a Sql Server and the software is leaving hundreds of sleeping connections for each user. Is there anyway to clear these connection programmatically?
I just stared at the code and wondered how on earth it was the accepted answer. Then I clicked [+] Expand Code. D'oh!
Even Mien
2010-01-27 03:10:28
One more tip, copy and paste into WordPad first, then into SSMS.
Even Mien
2010-01-27 03:14:39
+1
A:
Which version of SQL Server do you run? You can write a stored procedure to do this, looking at the data from sp_who and then making some guess about the last activity. There's a "LastBatch" column that does the last time something was submitted by this user. I'd say if that is over an hour old (or whatever interval), execute a KILL for that SPID.
You could do this in SQL 2005 like this:
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
+1
A:
But, instead of killing these processes manually, shouldn't there be a way to avoid these? I have the same problem in a project
In our Web application, we are performing some updates using a Web service (i.e. a program calls a webservice method. The method opens a connection, does an update, commits and closes the connection using connection.close())
In the sqlserver mgmt studio if I do a sp_who2, I see that the connections increase as teh app is running - in fact at the rate of 1 connection per update execution. AND the concerning part is that it crosses the 100 and then does not allow more connections into the db. Users are not able to login as well the programs fail as they cannot get any more new connections.
How to ensure that the connections are reused - We have not written any connection pooling code, using the default asp.net and Sqlserver connection pooling. Why are the connections not be reused and why are they not vanishing after being "Closed" ? Does this depend on the fact that a webservice is handling the transaction ?
Thanks a lot