Runaway App: How to free memory and free up unused db connections.
Posted on 2007-11-23
I have a third-party billing system installed on a win2003 server. The system comprised of IIS (asp) apps using VB6 COM Components as middle tier, on top of a SQL Server 200 database.
The company cannot be contacted to resolve the problems I am having, so I need to resolve it myself.
Roughly each day (not always the same time) the server stops responding to RDP and HTTP connections. A ping responds fine, but I have to ask the co-lo company to restart the server each time.
The error log has errors from an agent exe program that runs every 5 minutes or so. Its a process that expires user accounts based on time used etc. This process throws errors just before the server stops responding, saying that the database connection timed out.
I looked just now at the Current Activity/Process Info tab of the SQL Enterprise Manager, and there are 41 connections sat idle. Would these cause a problem if they are idle? Even if not, it worries me that they might take up memory. Usually just before the server hangs, the SQL Server process is consuming 600+ MB of memory. After a fresh start and with the system being actively used, normally it is around 80-100MB.
I am guessing that the scheduled billing expiry process either is running a query that is badly optimised, or that the exe doesn't release its objects/memory correctly. Either way I REALLY need to fix this asap. I dont have access to the source code for the exe, not do I know exactly what actions it performs.
We are in the process of moving the system to another system, but would like to resolve this before the migration process, as it is a little time away.
It is not a heavy use system, in fact usage is very light. It is a prepaid, time-based billing system for public network users. We have perhaps 100 users per day. The billing server is only contacted for session start, ping updates, session stop requests and account queries. It ran much better than this previously when we had more clients, on the same hardware.
All ideas very gratefully received! (I am a mainly a developer, not a db admin.)