Solved

VB - SQL Server memory problem

Posted on 2000-05-08
7
179 Views
Last Modified: 2011-09-20
we have several servers with sql server 7 and identical databases on each.
so far 2 of them have been using more and more memory i.e. initially about 100000K and increasing steadily over several days or weeks to over 1000000K.
we have several VB programs running on the severs. one of them is continually polling a table in the database - checking to see if there are any new records and if there is they are processed and deleted.
we are using RDO to access the database
and the recordsetset is never closed i.e. it is reset every time we poll the table. the timer controlling this has an interval of 1 millisecond.
after processing the records are transmitted to other computers using a tcp control - i dont know which.
also CPU usage steadily increases even though there is very little activity.
eventually the computer comes to a halt and must be rebooted.
any ideas what is causing these problems




0
Comment
Question by:adl
7 Comments
 
LVL 2

Expert Comment

by:cjwik
ID: 2791531
It is hard to tell with out looking at the code, but it is allways a good rule to close the recordset and set the object to nothing when your are done with it. This is so you don't have to end up with problem like this.
0
 
LVL 4

Expert Comment

by:amit_panjwani
ID: 2791547
Increase timer interval, Keeping it to one milli second hogs server resources.
0
 

Expert Comment

by:vgru
ID: 2791629
adl:
You should not have the polling vb application runnibg on the same machine as the sql server.  
I have exactly the same problem that you have with a vb application pooling for data continuosly.  I tried everything to reduce the memory and cpu usage but there was little to be done.

I had to put the polling application on a machine of it's own so that it would not slow down the server because users where complaining that the server was not responding to sql calls because the vb application was stalling all the cpu and memory.

Hope this helps.  
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 2

Expert Comment

by:aidan_gill
ID: 2791680
I had the same problem for weeks, with VB running like a dog and then hanging.
The reason for this was that there was an ODBC trace going on in the background and the SQL.log file was 800mb, so I stopped the trace in control panel/odbc/tracing and went into DOS and deleted the log.

hope it helps
Aidan
0
 

Accepted Solution

by:
biswaspradeep earned 200 total points
ID: 2791851
This type of problem may occur due to the following reasons:
1. Either the SQL log file gets increased dramatically and the log doesn't gets cleared once the transaction is committed. If this is the case you need to uninstall SQLServer and reload it.
2. Increase the Transaction log size of your database.
3. And it is always a good practice to close the recordset when it is not being used as stated by some other expert.
4. Increase the virtual memory of the system.
0
 
LVL 2

Expert Comment

by:cjwik
ID: 2791966
Regarding answer, point no 1.

You are correct that this might be an area to look in to, but you don't have to reinstall! It is possible to truncate the log on the "fly"...

no 2. Increasing the log space is one way, another is to take a backup whiche will clear the log as well. What you should do is depening on when you can do the backup ....
0
 

Expert Comment

by:rfyl
ID: 2805595
Another solution is to use Triggers and Stored Procedures in your SQL Server 7 database to process/delete any new records.

This was you do not have to continually poll the database.

-----------------------------7d026221b68
Content-Disposition: form-data; name="notify"

on
-----------------------------7d026221b68
Content-Disposition: form-data; name="qid"

10340963
-----------------------------7d026221b68
Content-Disposition: form-data; name="Submit"

Submit
-----------------------------7d026221b68--
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

744 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

14 Experts available now in Live!

Get 1:1 Help Now