March 14, 2002 at 11:56 am
My production SQL Server is experiencing a memory leak. It is most serious when running applications. I have to restart it each morning because memory usage can be as high as 1.1GB after a day of processing.
But it is occurring even when no applications are running against it
For example, if I stop and start at 5:00pm Friday, by Monday at 8:00am memory use will be in the order of 250MB after starting at about 23MB. No processing is performed over the weekend.
I am running SQL Server 7.0 (SVP 3) on Windows 2000 (SVP 2).
Hardware is a dual 933 Mhz processor Compaq DL 380 with 1.2MB RAM.
Any and all suggestionson how to troubleshoot this will be greatly appreciated.
March 14, 2002 at 12:28 pm
Note: SQL Server loves memory and will negotiate with the OS to give it the most it possible can (usually around 90%). This is to give it the ability to access the memory faster than f it has to negotiate at the tim of query execution. ALso som data is cached in memory as well as execution plans. This is all normal. If you don't want the server to use that much memory then right click it in Enterprise Manager and select properties, then find Memory settings and set the maximum memory SQL can use.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 14, 2002 at 2:29 pm
Many thanks for your prompt response. Now I can rest easy, but still have to convince a sceptical colleague!
March 16, 2002 at 12:38 pm
A question that should be asked is were there any issues with the amount of memory SQL Server was using or was there simply a concern?
One thing to remember about memory leaks is that in a lot of cases the memory lost isn't visible to counters. We had that happen to us due to a problem with USQM and Terminal Server under Windows 2000. The leak wasn't visible and actually resulted in server crashes. If you're mildly interested, here's the Q article that resulted out of our consultation with Microsoft:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q317855
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
March 18, 2002 at 9:23 am
Thanks Brian. It was a concern as there seemed to be no performance implications.
March 18, 2002 at 10:50 am
Weel, I use Windows 2000 with SQL Server, and lately I´m having the same problem. Every week we need restart our server because amount of memory mysteriously disappeared.
In "Task Manager/Proccess" I cant find what is using this memory.
My question is : Can SQL Server alloc this memory for some cache job, and dont deallocate it?!
March 18, 2002 at 11:16 am
SQL rarely deallocates memory once it has it. Does it happen gradually or all at once?
Andy
March 18, 2002 at 12:28 pm
Gradually. I'll try to use "Performance Monitor" to try detect where is this leak.
March 18, 2002 at 1:08 pm
Does anything else run on the SQL Server box? Is there a big batch job that occurs once a week or something of that sort?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
March 19, 2002 at 5:12 am
In fact, we use one program (made with Delphi - BDE) to process some TXT files and execute BULK INSERT into databases.
Maybe this could be one problem "Delphi + BDE + BULK INSERT".
March 19, 2002 at 5:21 am
Yes your leak could exist and be acually coming from the delphi object. Was this built inhouse or third party, if third party check with the vendor on this or if in house tyou may want to try on another machine testing to see if produce the same effect. This does not mean you could not have a corrupt dll that SQL uses that is causing this. If you cannot reporduce with the delphi object then try reinstalling the last or latest service pack for sql and see if clears. If not there are a lot of things to check. Also is SQL and this object the only thing running on this box (no IIS, or something else)?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 19, 2002 at 6:01 am
Yes. This machine is dedicate to DB server and to process this files.
Thanks for the tips. I think I'll have one full week over this.
I'll post new my tests results (if I find something).
Thanks again.
March 21, 2002 at 4:22 am
Could you check the mdac version. My sql7-servers using mdac 2.6 have enormous VM-sizes which are nomore visible after instaling mdac 2.6 sp1.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 22, 2002 at 8:56 pm
We had two identical machines that ran SQL Server 7.0 and nothing else. One machine ran fine. I restricted the memory in the server properties and it behaved as expected. The other machine would continue to eat memory as long as it was available. I tried reapplying service packs and nothing helped. I removed and reinstalled SQL 7.0 to no avail. I never did figure out what was wrong with that machine....
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)
April 1, 2002 at 5:30 am
Unless the first was a backup and restore of the second you most liekly had differences between machines. Not to mention DLLs that could have corrupted registry entries and type of hardware differences can lead to unmatched variances between the machines. Had to be something different in some way no matter. As long as SQL doesn't quash the OS and runs it generally is no an issue.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply