• RSS
  • Twitter
  • FaceBook

Welcome to ISAserver.org

Forums | Register | Login | My Profile | Inbox | RSS RSS icon | My Subscription | My Forums | Address Book | Member List | Search | FAQ | Ticket List | Log Out

ISA2004 --> MSDE connection leaks memory and takes server offline after N days.

Users viewing this topic: none

Logged in as: Guest
  Printable Version
All Forums >> [ISA Server 2004 Firewall] >> Logging and Reporting >> ISA2004 --> MSDE connection leaks memory and takes server offline after N days. Page: [1]
Login
Message << Older Topic   Newer Topic >>
ISA2004 --> MSDE connection leaks memory and takes serv... - 4.Aug.2005 4:49:00 AM   
MRIS

 

Posts: 58
Joined: 4.Aug.2005
Status: offline
After a clean install of ISA2004-SP1 onto a SBS2003 server, I configured the maxmemory for the MSFW instance of MSDE to consume no more than 30MB. Leaving RAM for Exchange, the full SQL server, the ISA cache, the file server etc. After about 8 days or so of operating normally, the MSDE server refused to accept connections, even from enterprise manager. Because the firewall service lost connection to it's SQL server, it decided to STOP, hence isolating the server from the network entirely (don't you hate it when that happens?). The SQL server had leaked memory. Using Enterprise Manager, one can see the current connections to the server and the resource utilisation on each connection. The memory consumption on the connections to all the actual ISA log databases were all normal, but there are 2 connections from the firewall service to the MASTER table of SQL MSDE server. It's one of these connections that appears to leak memory in MSDE.
I decided to upgrade the MSDE SP3 to SP4, but this didn't fix the problem. I also unrestricted the Maxmemory parameter. After a few days, the connection to the master table's memory consumption had exceeded 70MB and was well on it's way up. The query that gets run on this connection always appears to be the same one: "select name from master..sysdatabases".

I know that most of you on this forum are probably running their ISA2004 servers on dedicated boxes with 4GB of RAM, logging to a second dedicated SQL server, and think nothing of memory being consumed at 20MB per day without ever being released. So here's my first question: Has anyone else investigated it to this level and discovered the same memory leaking on this one connection (FW service --> SQL master table)? Is there any way to stop it from wasting memory? (establishing a scheduled task to restart the firewall service springs to mind. Is anybody doing this?)

Investigating internal SQL performance counters, it appeared that the firewall service was logging into the MSDE at a rate exceeding 1000 logons per second. Anyone noticed this?

OK, as a workaround, I configured all logging to be sent to text files only, then turned OFF logging altogether. Heres the weird thing, the Firewall Service maintains it's connection to the MSDE (even many reboots later), yep those 2 connections to the master table still get made. It even runs the same query on it, over and over. However without any other tables in the MSDE, the memory problem doesn't occur. This leads me to my second question: Is it possible to run ISA server without the MSDE SQL server present at all? (eg no logging required) Or "how can I stop the firewall service from making these inane connections to the MSDE MSFW SQL DB that it's not even supposed to be using??"
Post #: 1
RE: ISA2004 --> MSDE connection leaks memory and takes ... - 4.Aug.2005 9:37:00 AM   
MRIS

 

Posts: 58
Joined: 4.Aug.2005
Status: offline
ok, to answer my own question #2, using add/remove programs to remove the ISA2004's "Advanced logging" feature would uninstall MSDE and presumably remove the firewall service's desire to connect to it. The leaking memory question is still open. Isn't everyone's ISA2004 server running out of memory as the SQLSERVER process grows and grows?

(in reply to MRIS)
Post #: 2
RE: ISA2004 --> MSDE connection leaks memory and takes ... - 4.Aug.2005 9:42:00 AM   
LLigetfa

 

Posts: 2187
Joined: 10.Aug.2004
From: fort frances.on.ca
Status: offline
It is not a memory leak. It is MSDE performing as designed. There are several posts here that discuss it and provide commands to limit its consumption of memory.

(in reply to MRIS)
Post #: 3
RE: ISA2004 --> MSDE connection leaks memory and takes ... - 4.Aug.2005 9:57:00 PM   
MRIS

 

Posts: 58
Joined: 4.Aug.2005
Status: offline
the point is that I DID limit it's memory, to something generous, like 30MB, but after about 8 days the MSDE stopped taking connections, either from the firewall service, or from enterprise manager. Once this happens the firewall service STOPs, and the server disconnects from the network entirely. I want to know why the 30MB (or 50MB?) limit won't work, in that the SQL server exhausts it all then fails.

(in reply to MRIS)
Post #: 4
RE: ISA2004 --> MSDE connection leaks memory and takes ... - 4.Aug.2005 10:10:00 PM   
LLigetfa

 

Posts: 2187
Joined: 10.Aug.2004
From: fort frances.on.ca
Status: offline
LOL
30 meg, Generous?

I gave MSDE on my ISA server 1 gig to play with.

Now THAT is generous!

(in reply to MRIS)
Post #: 5
RE: ISA2004 --> MSDE connection leaks memory and takes ... - 4.Aug.2005 10:26:00 PM   
MRIS

 

Posts: 58
Joined: 4.Aug.2005
Status: offline
look what's happening on the MSDE:
Logging of FW traffic into a single table in a single DB. Logging of WWW traffic into another single table in a single DB. Each DB is closed and a new one created daily. This means there's not a lot happening on this MSDE. There aren't any complex queries, inner joins, complex index updates etc. Furthermore, the SQL memory resources associated with the FW and WWW DB files themselves are minimal. It's only the long-maintained connection of the firewall service to the MASTER database that consumes ALL the memory. What could you possibly need 1.7GB of RAM for when communicating with the MASTER DB in the MSDE? NOTHING! I mean, sure it's got a list of DBs, and ownership stuff and config data and stats and stuff in it, but does it really need 1.7GB of RAM to process a 1MB DB?

(in reply to MRIS)
Post #: 6
RE: ISA2004 --> MSDE connection leaks memory and takes ... - 5.Aug.2005 1:00:00 AM   
MRIS

 

Posts: 58
Joined: 4.Aug.2005
Status: offline
OK, digging a little deeper, turns out that there's an ISA2004 Alert called Log Failure that, guess what - STOPS THE FIREWALL SERVICE - !

OK, so now I can keep the firewall service running after the sql server exhausts all it's memory, so at least we've got a work-around...

(in reply to MRIS)
Post #: 7
RE: ISA2004 --> MSDE connection leaks memory and takes ... - 8.Aug.2005 3:46:00 AM   
MRIS

 

Posts: 58
Joined: 4.Aug.2005
Status: offline
At the moment I'm experimenting by creating a SQL Agent scheduled job, with a single Transact SQL statement: "KILL 52". This closes the sleeping connection to the master database (the one that consumes all the memory). A few seconds later the firewall service simply reconnects, doesn't even log enything or raise an alert, and everything just keeps ticking. I know this looks like a quick and dirty hack, but it's a workaround that keeps the SQLserver memory usage trim enough to be able to keep running indefinitely using only a finite amount of memory. The PID of 52 seems to be constant, in that it always correlates to the process consuming the memory. Given the dedicated nature of the MSDE and deterministic behaviour of the system, I'm sure just killing PID every 3 days is a valid work-around.

(in reply to MRIS)
Post #: 8
RE: ISA2004 --> MSDE connection leaks memory and takes ... - 2.Sep.2005 11:00:00 AM   
MRIS

 

Posts: 58
Joined: 4.Aug.2005
Status: offline
after monitoring this for a while, it was obvious that the ID (to be killed) changed after the job engine was enabled. I thus had to create a more sophisicated transact-SQL job as follows, it just kills any firewall connections consuming more than 30MB:

declare @dbname varchar(30), @spid varchar(10)
declare spids cursor for
select convert(varchar, spid) from sysprocesses
where program_name = 'Microsoft Internet Security and Acceleration Server 2004' and memusage GREATERTHAN 30000
open spids
while(1=1)
begin
fetch spids into @spid
if @@fetch_status LESSTHAN 0 break
exec('kill ' + @spid)
end
deallocate spids
go

REPLACE GREATERHTAN and LESSTHAN with the symbols.

(in reply to MRIS)
Post #: 9
RE: ISA2004 --> MSDE connection leaks memory and takes ... - 17.Oct.2005 4:13:00 PM   
Guest
Hello:
If you don't want to use OSQL.EXE or the SQL Query Analyzer to modify MSDE's maximum memory setting, there's a script for doing this at www.ISAscripts.org named "ISA_MSDE_Max_Memory.vbs".

(in reply to MRIS)
  Post #: 10

Page:   [1] << Older Topic    Newer Topic >>
All Forums >> [ISA Server 2004 Firewall] >> Logging and Reporting >> ISA2004 --> MSDE connection leaks memory and takes server offline after N days. Page: [1]
Jump to:

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
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts