You may experience high memory usage on a computer that is running ISA Server 2004 or ISA Server 2006 and that logs messages to an MSDE database

Article translations Article translations
Article ID: 909636 - View products that this article applies to.
Expand all | Collapse all

SYMPTOMS

You may experience high memory usage on a Microsoft Internet Security and Acceleration (ISA) Server 2004 computer or ISA Server 2006 computer that is configured to log messages to a Microsoft SQL Server Desktop Engine (MSDE) database.

CAUSE

Microsoft SQL Server uses the available physical memory to optimize MSDE insertion and query processes. SQL Server is designed to release physical memory as soon as there is a request for physical memory from other processes that are running on the computer.

Note This behavior by SQL Server does not affect the regular operation of other processes on the ISA Server computer.

STATUS

This behavior is by design.

MORE INFORMATION

Although this behavior does not affect the regular operation of other processes, you may want to limit the amount of physical memory that is allocated for SQL Server. Recommended values are shown in the following table.
Collapse this tableExpand this table
System memoryRecommended setting
1 GB386 MB
2 GB512 MB
3 GB764 MB
4 GB1024 MB
However, you should monitor the memory performance counters, the SQL Server log (Sql.log), and the ISA Server logs to make sure that logging is not affected. If you experience problems, increase the recommended values gradually. To do this, follow these steps:
  1. Determine the maximum memory limit that is allocated for SQL Server. To do this, follow these steps:
    1. Paste the following text into a text editor such as Notepad, and then save the file by using a ".sql" extension. For example, save the file as “checksqlmemory.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
    2. At the command prompt, type the following command:
      osql -E -S ServerComputerName\msfw -i Path\checksqlmemory.sql
      Note In this command, checksqlmemory.sql is the file that you created in the previous step and Path is the full path of the file that you created in the previous step.
  2. Reduce the amount of physical memory that is allocated for SQL Server. To do this, follow these steps:
    1. Paste the following text into a text editor such as Notepad, and then save the file by using a ".sql" extension. For example, save the file as “setchecksqlmemory.sql.”
      USE master
      EXEC sp_configure 'show advanced options', 1
      RECONFIGURE WITH OVERRIDE

      USE master
      EXEC sp_configure 'max server memory (MB)', MaxServerMemory
      RECONFIGURE WITH OVERRIDE

      USE master
      EXEC sp_configure 'show advanced options', 0
      RECONFIGURE WITH OVERRIDE
      Note In this example, MaxServerMemory is the value of the physical memory in megabytes (MB) that you want to allocate. For example, replace MaxServerMemory with “512.”
    2. At the command prompt, type the following command:
      osql -E -S ServerComputerName\msfw -i Path\setchecksqlmemory.sql
      Note In this command, setchecksqlmemory.sql is the file that you created in the previous step and Path is the full path of the file that you created in the previous step.

    Note The optimum physical memory allocation for SQL Server may vary for your system.
Follow these steps to reduce the memory used to 512 MB:
  1. Create and name a batch file. For example, name the file SetMSDEmemory.bat.
  2. Paste the following text as a single line, even if it appears wrapped in your display. Then, add a second line with the single word PAUSE:
    osql -S %computername%\MSFW -E -Q "exec sp_configure 'show advanced options',1 reconfigure with override exec sp_configure 'max server memory (MB)',512 reconfigure with override" 
    PAUSE
  3. Log on to the ISA server as the administrator.
  4. Run the batch file. This will produce output, such as the following:
    DBCC execution completed
    Configuration option 'max server memory (MB)' changed from 204754 to 256.
    Note If the DBCC prints error messages, note the error code and contact PSS for help with changing the configuration and for more troubleshooting.
  5. Press any key to exit the command shell.

Properties

Article ID: 909636 - Last Review: February 18, 2009 - Revision: 4.0
APPLIES TO
  • Microsoft Internet Security and Acceleration Server 2004 Standard Edition
  • Microsoft Internet Security and Acceleration Server 2004 Enterprise Edition
  • Microsoft Internet Security and Acceleration Server 2006 Enterprise Edition
  • Microsoft Internet Security and Acceleration Server 2006 Standard Edition
Keywords: 
kbhowto kbtshoot kbprb KB909636

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com