MRIS
Posts: 58
Joined: 4.Aug.2005
Status: offline
|
The answer is that there is a memory leak in the SQL server (MSDE) that even if you update the MSDE with Service Pack 4 for SQL Server MSDE, doesn't get resolved. The memory is consumed by a single client connection to the MSDE, which just grows over time and never shrinks. The connection is from the firewall service to the MSDE's master table, and is used exclusively to repeatedly query the database for INANE information such as the full path names of the actual database files it has. It's essentially the firewall service inventorying the database about it's files, quering their size, and how many there are etc etc. However, left unchecked, this client connection's memory allocation just grows and never shrinks. But here is the kicker, simply right-clicking on the connection using enterprise manager and saying "kill connection", does just that. The firewall service doesn't miss a beat and merely reconnects and continues asking it's INANE questions. However all the memory previously occupied by the just-killed connection is released, and the process of the new client connection growing it's RAM use starts again.
So how do you fix it? Easy, there are a few choices:
a. don't use the MSDE for logging, rather just log to text files. - disadvantages is that logging is much poorer under this config, with loss of some reporting features.
b. don't log at all. - disadvantages speak for themselves, but it's a valid option.
c. use a full copy of SQL server instead - disadvantages include that the firewall can't automatically manage the size of the database, this has to be managed manually.
d. Do what I did: Create a SQLAgent scheduled task using enterprise manager that queries the database for any connections that are all of the following:
1. to the master table
2. from the firewall service
3. consuming more than 40MB of RAM.
(it will only ever find one that matches all of these conditions)
- kill it.
That's exactly what's happening here, and it does it on all instances of ISA2004 with SP1 that log to the included MSDE. The people who haven't noticed it yet are happily ignorant of WHY their MSDE sqlservr.exe process is consuming 1.7GB of RAM, merely assuming it's using all that RAM as part of its normal functioning (eg buffers, index tables etc), not realising the subtelty of what's really going on; one buggy firewall services' simple, single connection to the master table repeatedly quering simple stuff that shouldn't need hundreds of megabytes or even GBs of RAM to efficiently process. Also most people don't "limit" the amount of RAM the sql process can consume, allowing it to grow unbound, thus the actual stopping of the firewall service bug doesn't occur within the lifetime of the session and they don't see it (eg they reboot their box every few weeks for other reasons). I only noticed it myself after I had limited the memory allowed for the SQL process to 23MB, causing the memory exhaustion effect to occur within about 3 days on a small 5-user LAN. Once the memory is exhausted, you can't even connect to the MSDE using enterprise manager, and neither can the firewall. So I wrote a script that is automatically run once a day that tracks down this rogue memory-soaked connection from the firewall service to the master table and simply kills it.
|
|
My Subscription |
My Forums |
Address Book |
Member List |
Search |
FAQ |
Ticket List |
Log Out
Printable Version
ISA SQL Instance Using a Lot of Memory - ![""]("http://www.SBS-Rocks.com/images/ISAmemory.jpg")
RE: ISA SQL Instance Using a Lot of Memory - 
New Messages
No New Messages
Hot Topic w/ New Messages
Hot Topic w/o New Messages
Locked w/ New Messages
Locked w/o New Messages
Post New Thread