September 26, 2004 at 9:20 pm
Hi all,
I've written a program using VB6 and SQL2000. When I run the program the memory amount used by SQL2000 increase from 6 MB to 150 MB, then I stop the program and...it's strange, the memory did not be released as it should.
Anyone knows how to force SQL server to release memory, please help!!!!
Thanks and Regards
September 26, 2004 at 9:58 pm
Not sure, should happen automatically. Are you correctly terminating the connection from the VB app to SQL Server? (Set connection = null etc).
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 26, 2004 at 10:05 pm
Thank Parkin for reply so soon, I have called connection.close() before terminating the VB app. I have found that SQL server will release memory after a amount of time (happen automatically as you said) but that I want is to inform SQL server to release memory as soon as possible, please advise me.
Regards,
Tuan
September 27, 2004 at 1:41 pm
When you talking about SQL Server memeory, do you mean, SQL Server memory that takes up from O/S? If that is the case, once SQL server reserve the memory from O/S, it won't release back to O/S but just hanging there for later use.
Let us know if that is the case.
September 28, 2004 at 8:32 am
Even though you close the connection in VB, SQL Server keeps the connection open for a while anyway. This is like caching, it is to speed up reconnecting if your application is disconnecting/reconnecting all the time.
Sorry, I don't know how to change the connection close timeout.
Make sure you are doing:
connectionname.close
set connectionname = nothing
Make sure you are also getting rid of all the forms in memory before shutting down system (you should really clear form out of memory when it is no longer needed, if you are not already doing it):
For Each frm In Forms
Unload frm
Set frm = Nothing
Next frm
Regards
Peter
September 28, 2004 at 4:02 pm
SQL Server dynamically adjusts the amount of memory it uses. It will automatically take as much memory as it needs up the total amount installed on the server! It then keep the memory even if it doesn't need it as there is a good chance that it will need the same amount again in the future. However, if another application starts up on the server, then SQL server will automatically release as much memory as the new application requires.
The main point of this is to eliminate paging which causes poor performance.
If the new application requires more memory than is available, even after SQL has given up most of the memory, then you will get paging anyway and your system will perform badly. Ths is why it is better if SQL is run as the only application on a server.
So on most SQL servers which have a reasonable workload, you will fnd that the memory usage is very close to 100% all the time. This is by design and is nothing to worry about.
October 5, 2004 at 7:14 am
if you have small memory on your computer (server), the best solution is to adjust SQL Server memory and make it FIXED
so in Enterprise manager do this:
1- right clcick on your server and choose (properties)
2- go to memory tab
3- check on (use a fixed memory size) and alocate it in trackbar under it ... and a number is writen beside (use a fixed memory size) indicate how much memory you use
4- do not check on (Reserve physical memory for SQL Server)
by the way you can change (Minimum query memory) to 512 if you need, but it is not recommended.
i hope this help u
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply