Solved

Runaway App: How to free memory and free up unused db connections.

Posted on 2007-11-23
16
481 Views
Last Modified: 2013-11-25
Hi.  

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.)

Thanks, TheFoot
0
Comment
Question by:Barry Jones
  • 8
  • 5
  • 2
  • +1
16 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
ID: 20340952
Hi,

  please try to just lower the maximum server memory setting:
  http://msdn2.microsoft.com/EN-US/library/ms178067.aspx

  set it to 200MB for example, and your "trouble" should go away...
0
 
LVL 31

Assisted Solution

by:James Murrell
James Murrell earned 50 total points
ID: 20341096
i think this would kill connections - may help

SELECT spid FROM SYSPROCESSES  
WHERE datediff(mi,last_batch,getdate()) > 1

"1" is the time in minutes, take care  it list all spid and you have to list a user account:

SELECT spid FROM SYSPROCESSES  
WHERE datediff(mi,last_batch,getdate()) > 1 AND loginame = 'name_of_user'

then use the comman KILL spid.
0
 
LVL 12

Author Comment

by:Barry Jones
ID: 20341133
Thanks angelIII.  I have set that and will let you know what happens.  Will this not cause performance issues with the application?
0
 
LVL 12

Author Comment

by:Barry Jones
ID: 20341158
Thanks cs97ijm3, I will try that once the server starts to overload again.
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 20341166
cool i hope angelIII solution works for you
0
 
LVL 12

Author Comment

by:Barry Jones
ID: 20341471
I dont know if this is relevant, but the "NetBios Wins Service" has a corrupt description "½âÎöºÍ»º³åNETBIOSÃû (WINS) Ãû³Æ¡£".  Executable is "svchost.exe -start"

I also have a service called "Yahoo Services" running.  Executable is "yahoo.exe /service"?  I have run spyware and AV scans, but nothing gets picked up.  

Is it possible that spyware is querying the database and hogging the resources causing my app to have the connection timeouts?  How would I check this?

Thanks, TheFoot
0
 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 50 total points
ID: 20341618
I would not immediately assume that the reason for the server stopping responding to connections is related to sql server. The usual troubleshooting step is disabling all unnecessary applications and seeing if the problem goes away, thus isolating the  culprit. Yahoo.exe would probably be the first one to kill.

Also, you can run performance monitor, have it logging things like memory, paging, cpu usage, etc. - everything that potentially might be the reason. Performance monitor would be flushing every new record to a file, so after the server reboots,  you would review the file and at least see if the reason was in cpu usage or in memory.
0
 
LVL 12

Author Comment

by:Barry Jones
ID: 20342795
angelIII , this morning the memory usage appears to have stabilized under the limit that I set according to your advice.  I will know for sure if we get to tomorrow morning and all is still well.

vadimrapp1, thanks for your advice.  I have setup perfmon logs and if a crash happens again, then I will investigate to see what these hold.

Also, I discovered that my anti-spyware app was corrupt or something wrong with it.  Scans were coming back clean.  I uninstalled and reinstalled it and it has (so far - still running) picked up a shedload of spyware!!  

More updates later.  Man this server is in a shite state! :)
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 20342838
> shedload of spyware!!


...was 99.9% the reason of the problem.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 20342856
...so I wouldn't even bother with performance logs etc. On the other hand, if the server was so exposed to threats, it's probably good idea to scan it with other anti-virus and anti-spyware products as well; the best would be to reformat it.
0
 
LVL 12

Author Comment

by:Barry Jones
ID: 20342970
Yes I agree.  We are moving the system over to a complete new server during the next 2 weeks, so I just need to keep it limping along until then!

Thanks everybody for your comments.  Once all is well tomorrow morning, I will close off this topic.

Cheers, TheFoot
0
 
LVL 12

Author Comment

by:Barry Jones
ID: 20345530
:)

this morning the memory usage is the same, the server didn't hang and all looks darn good!

Thanks everybody for your advice, and thanks angelIII for the fix.

TheFoot
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 20345643
actually, now that the real problem (spyware) is found and fixed, I'd suggest that you remove the limitation on sql server memory. What you said "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" is perfectly normal - it increases the memory for its own cache of data and execution plans. Less memory, less performance. It's best to allow it to manage its memory automatically.
0
 
LVL 12

Author Comment

by:Barry Jones
ID: 20345650
Hi vadimrapp1.  Thanks for your comments.  I take what you say, I think I will increase the setting again, but still keep a limit on it so that I don't get locked out of remote access.

I will monitor the app performance while I increase the limit.

Thanks again..
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 20345765
> still keep a limit on it so that I don't get locked out of remote access.

no need to. SQL server's own memory managing ways are sufficiently smart and sufficiently tested. It won't starve anything, and will release the memory it has acquired if the need arises. Generally, it takes memory just short of increasing the paging in the system. I suggest you read "Dynamically Managing Memory on Windows NT and Windows 2000" in BOL. For best results, download and run Best Practices Analyzer Tool, http://www.microsoft.com/downloads/details.aspx?FamilyID=b352eb1f-d3ca-44ee-893e-9e07339c1f22
0
 
LVL 12

Author Comment

by:Barry Jones
ID: 20347908
Then the problem must be something else. It just happened again tonight.  I am going to look at the server logs tonight, but we are now going to pull the system out much earlier than we planned, so please don't spend any more time on this... :)

Thanks for your help...
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now