Richard Hicks' Blog

When Forefront TMG 2010 is installed, an instance of SQL Server 2008 Express is also installed to facilitate local firewall and web proxy logging. Some TMG administrators have reported that the SQL server process (sqlservr.exe) may consume an excessive amount of memory.

This occurs because SQL server performs its own internal memory management. By design it will consume large amounts of memory and hold it in reserve, which may appear to be a memory leak. However, if you observe the memory consumed by SQL server over an extended period of time, you will notice that it will periodically release memory as well.

In most cases, allowing SQL to handle the job of managing its memory without restriction poses no real problem. However, if your system is exhibiting signs of high memory pressure and you are confident that there are no other processes that are consuming excessive amounts of memory, you can configure SQL to limit the amount of memory that it will reserve. Before manually configuring SQL memory limits, review this blog post for a list of recommended settings.

Next, open an elevated command prompt and enter the following command:

osql –E –S .\msfw

After connecting to the SQL instance, enter the following commands:

USE master
GO

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC sp_configure 'max server memory (MB)', 1024 --configure 1GB limit, adjust as needed
GO

EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

This script assumes that you’ve chosen a 1GB (1024MB) memory limit. You can adjust the script above to reflect the values for your environment as required. Once complete, restart the SQL server service for the changes to take effect.

To view the currently configured memory limit, simply omit the numeric value after max server memory (MB), as shown here:

EXEC sp_configure 'max server memory (MB)'

To make things easier you can download these as script files and execute them using the following command:

osql –E –S .\msfw -i <path to script file>

You can find script files to show and set SQL memory limits here:

showsqlmem.sql
setsqlmem.sql