Huge number of User Connections!

[Antzz]

Hi Guys,
We have a SQL 2005 server hosting multiple databases and serving number of web applications(located on a separate web server) as well as windows applications. At any moment of time, there are normally 100-200 users. We see the number of user connections rise as time passes(from the last SQL re-start) and it goes up to 36K connections within a month. We are at a loss to explain on what is generating these connections.

Any ideas on how to troubleshoot this issue - possibly find the rogue application generating these connections?

Thanks and appreciate it.

A

 

[mrdenny]

Sounds like one of more of the web servers isn't closing the connections to the SQL Server.

Check the sysprocesses table and see when when the last batch was completed for the older processes.

Hopefully you will be able to identify the application based on the application name column, hostname, or login name that the stale connections are using.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Antzz]

I checked the sysprocesses table and couple of things stood out.
1) I have quite a few processes of lastwaittype"MISCELLANEOUS" and their status is either "dormant" or "sleeping"
2) There were also quite a few processes of lastwaittype 'SOAP_WRITE' with status "done" but still showing up on sysprocesses

Does any of these make any sense to you?

Thanks

A