A lot of Microsoft server products are using Microsoft Data Engine (MSDE) which is based on Microsoft SQL Server technology. ISA 2004 is one of these products. I recently began having an "issue" with memory usage on my ISA 2004 and did some digging to find this solution!
I was having the following error show up in my application event log and tracked it down to a memory usage issues with MSDE.
Event Type: Error
Event Source: Microsoft Firewall Event
Category: Log Event ID: 21192
Date: 4/26/2005 Time: 12:00:15 AM
User: N/A
Computer: ISA
Description: The Microsoft Firewall was unable to connect to MSDE database. The MSDE Error description is: SqlDumpExceptionHandler: Process 54 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Well it turns out that the max server memory setting in MSDE (and SQL) defaults to 2147483647Mb!! A little more digging and I found a SQL script to change this memory. Copy the following into notepad and save as sqlmemorylimit.sql .
USE master
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
USE master
EXEC sp_configure 'max server memory (MB)', 64
RECONFIGURE WITH OVERRIDE
USE master EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
With this SQL script saved we can now run it with the following command.
osql -E -S servername\MSFW -i c:\sqlmemorylimit.sql
This will set the memory limit for MSDE to 64MB. Now you are probably thinking that 64MB is awefully low, and you are right. The box in question only had 256MB ram, however I would suggest to set this to 1/4 of the total amount of physical memory. As an example, if you have 1GB of memory, set this to 256MB.
If you wish to verify the memory limit copy the following code into notepad and save as sqlmemorycheck.sql .
USE master
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
USE master
EXEC sp_configure 'max server memory (MB)'
USE master
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
Then run the script with the following command.
osql -E -S servername\MSFW -i c:\sqlmemorycheck.sql
This will display the memory limit that is set for MSDE.
