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??"
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?
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.
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?
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.
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.