I installed a Windows 2003 Service Pack 1 server with ISA 2004 Standard edition Service Pack 1. After a few days logging on to the ISA server I got the error  “Out of memory”. After checking the Task Manager to find out what process was the problem I noticed it was SQLServr.exe. It was using about 1,7 Gb of memory. As there is only 2 Gb of memory and some other processes also require some memory, I wanted to know how I could lower the usage of the SQL Server (MSDE).

First I tried to connect to the SQL Database from a SQL 2000 Enterprise Manager which is running on a SQL 2000 Cluster. But ofcourse this one couldn’t reach the SQL Server running on the firewall. I created a new Access Rule on the ISA server to allow connection from the cluster to the ISA server on port 1433 (TCP) and 1434 (UDP), both outbound. Then I tried connecting to the database which had the name : $MSFW (as the service name has MSSQL$MSFW). This didn’t work, because of an authentication problem. It seems that MSFW runs as a local service (login is NT AUTHORITY\NETWORK SERVICE) and doesn’t have any logins
set up for network authentication other than “sa” and BUILTIN\Administrators.

Solution

So with the help of my friend, Google,  I found an article which helped me to limit the SQL server memory usage. This script was posted by Adar Greenshpon [MSFT].

First you can view the memory usage on the the server. To do this copy the following code to a text file (eg. c:\checkmemory.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

To check the maximum server memory (‘max server memory’) setting type the following from a command prompt:

osql -E -S %computername%\MSFW -i c:\checksqlmemory.sql

The following output was returned:

1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Configuration option ’show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.
 name                                minimum     maximum     config_value        run_value
 ———————————– ———– ———– ————        ———–
 max server memory (MB)                        4  2147483647   2147483647         2147483647
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Configuration option ’show advanced options’ changed from 1 to 0. Run the RECONFIGURE statement to install.

Now you can change the setting, using a seperate script and running it. In this example the max memory size will be set to 512 Mb. Paste the following code in a text file (eg ‘c:\setservermemory.sql’):

USE master
EXEC sp_configure ’show advanced options’, 1
RECONFIGURE WITH OVERRIDE
USE master
EXEC sp_configure ‘max server memory (MB)’, 512
RECONFIGURE WITH OVERRIDE
USE master
EXEC sp_configure ’show advanced options’, 0
RECONFIGURE WITH OVERRIDE

Again, run this with the following command :

osql -E -S %computername%\MSFW -i c:\setservermemory.sql

The following output was returned:

1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Configuration option ’show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Configuration option ‘max server memory (MB)’ changed from 2147483647 to 512.
Run the RECONFIGURE statement to install.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Configuration option ’show advanced options’ changed from 1 to 0. Run the RECONFIGURE statement to install..

You can view the setting by running the first command (see above). Now your server should be using less memory…

The following output was returned:

1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Configuration option ’show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.
 name                                minimum     maximum     config_value        run_value
 ———————————– ———– ———– ————        ———–
 max server memory (MB)                        4  2147483647          512                512
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Configuration option ’show advanced options’ changed from 1 to 0. Run the RECONFIGURE statement to install.