April 23, 2002 at 7:47 am
I'm running a SQL 7 database with a similar "mystery" memory leak. The machine (Windows 2000 Advanced Server) is only used for SQL Server and nothing else. Access is being used as a front end, and within Access all ADO connections are "set to nothing" after their use. Yet the memory doesn't get released. Is Access notorious for this?
Dana
Connecticut, USA
Dana
April 23, 2002 at 10:34 am
Set the max memory so SQLServer does not take more than it is allowed.
Also try using DBCC DropCleanBuffers
This would free up the cache. In turn it should free up some memory. Although I have not used DropCleanBuffers to free up memory, i would assume it should do the trick. May be you can schedule a maintenance plan to run every hour that issues this command. Note that there are other performance issues associated with that.
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
April 23, 2002 at 11:31 am
Ok let's try this again, SQL will try to keep cached as much as possible so that access to data resources can be as efficient as possible. Utilization of upto and sometimes over 90% is not a bad thing as it will give up any memory that the OS requires it too (may fight a bit but does happen). If your server pegs the 100% mark on memory and you start seing out of VM messages or the server completely stops responding give a mintue and see if goes away. This is usually a sign of a poor query or bad cursor and if the memory stays at 100% numerous times a day and for long periods of time then use profiler to find what is doing it or be ready to purchase memory. The use of DBCC DropCleanBuffers is really only suggested for testing as whe you clear the cache the stored data and execution plans are dropped from memory and recreated the next run (first run is slowest because of no cache to augment the speed of access). Aslo running DBCC DropCleanBuffers does not always free the allocation of any memory as SQL controls the memory not the data within, it can expand or contract based on SQL and OS negotiation over resources. By allowing it to use as much as possible then data being pulled into memory does not have wait for SQL to grab the needed resources, thus again a performance gain. Unless the box is suffering high waits and lots of exchange between memory and swap file then there is no reason to sweat it.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 25, 2002 at 1:08 am
Our SQL 7.0 is running also on a Dual Processor Proliant ML370. This machine has 640 Mb internal memory and is constantly running on 740Mb where the sql.exe is running 540Mb even though there are no processes running on SQL.
It seems that there are tools through which you can measure the exact mem usage. I haven't found it yet though.
The OS is NT4.0. We are accessing it through Terminal Server on a W2000 Server.
I wouldn't worry.
Kind regards
Jeffrey
JV
JV
April 25, 2002 at 4:40 am
I believe compaq and other vendors have these tools and other helpfull info normally on your install disks. If not check out your vendors site under support usually.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 25, 2002 at 9:25 am
DBCC DROPCLEANBUFFERS is not recommended in a production environment as Antares indicates. It, however, only frees up the Buffer Cache Pool, which corresponds to data. DBCC FREEPROCCACHE removes execution plans. Again, not recommended for production.
NT's Performance Monitor will allow you to track memory usage. If you look under SQL Server:Memory Manager there are counters for tracking memory reserved for connections, for the workspace, for locks, for the cache, and total overall. There's also a counter for target memory, which will allow you to see what SQL Server thinks it needs.
You say there is no processing occuring over the weekend. Are there maintenance plans firing off? How about backup plans or index rebuilds (custom scripted outside of a maintenance plan). One thing you could do to verify there is no activity is take a Profiler trace over the time period to see what does occur. Remember, SQL Server will expand memory usage for its own functions as well.
Another option you always have is limited the maximum memory SQL Server is allowed to use.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply