Home > Forefront TMG 2010, Threat Management Gateway > Configuring SQL Memory Limits on Forefront TMG 2010

Configuring SQL Memory Limits on Forefront TMG 2010

January 23, 2012

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

EXEC sp_configure 'show advanced options', 1

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

EXEC sp_configure 'show advanced options', 0

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:


  1. January 23, 2012 at 9:58 am

    Good tips. Most notice this when they move a SQL server to a virtualized environment. Memory allocated was perfectly calculated and right sized for the VM, then SQL sucks it all up like a vacuum. Linux also does this for file system operations as well, but that is a whole other topic.

    Great SQL statements provided though. Thanks for sharing

  2. Vuk
    January 23, 2012 at 11:45 pm

    Tnx for this, I’ve been looking for this.

    January 24, 2012 at 1:01 am

    Dear Richard,

    Thank you for the post.

    Unfortunately, I am not able to open a connection to SQL Express instance, however Name Pipe is enabled.
    Please find below for more information:

    C:\Users\LAB>OSQL -E -S ./MSFW
    [SQL Server Native Client 10.0]Named Pipes Provider: Could not open a
    connection to SQL Server [3].
    [SQL Server Native Client 10.0]Login timeout expired
    [SQL Server Native Client 10.0]A network-related or instance-specific error
    has occurred while establishing a connection to SQL Server. Server is not
    found or not accessible. Check if instance name is correct and if SQL Server
    is configured to allow remote connections. For more information see SQL Server
    Books Online.

    Please advise.

    Thank you and regards,

  4. January 24, 2012 at 7:30 am

    You are using the wrong slash. It should be .\msfw and not ./msfw. 🙂

    January 24, 2012 at 9:05 am

    Thank you Richard 🙂

    It works great!


  6. Colin Golden
    January 25, 2012 at 5:44 am


    I’ve a client using UAG2010 who perceived he has a memory leak as well.
    I assume the same SQL express instance is installed when UAG us used?

    If so, would it be safe to use the SQL commands on a UAG installation (It’s also an NLB array 😦 so I imagine you’d have to manually apply the script to each array member.)

    Thanks in advance,


  7. January 25, 2012 at 9:45 am

    Hi Colin,

    Absolutely. Forefront TMG 2010 is installed when UAG 2010 is installed, so this script will function in exactly the same way on a UAG server.

  8. Mohammed J.H
    May 31, 2012 at 12:59 am

    Hi Richard,

    I ran the command but i’m receiving the following:
    Note: osql does not support all features of SQL Server 2008.
    Use sqlcmd instead. See SQL Server Books Online for details.

    I have tried using SQLCMD but receiving the following:
    Sqlcmd: ‘-E’: Unknown Option. Enter ‘-?’ for help

    Any ideas?

  9. May 31, 2012 at 12:12 pm

    Hmmm…works fine for me. Not sure what the trouble is here. You should be able to replace OSQL with SQLCMD and use the same switches.

  10. Mohammed J.H
    June 3, 2012 at 11:24 pm

    Hello Richard,

    Thanks for the help, I got it working!
    Just FYI, It was a language problem! The TMG virtual machine first language was set to Turkish keyboard and its pretty close to English. I changed it to English and retyped the command and it works.


  11. June 8, 2012 at 1:30 pm

    Good to know. Thanks for sharing! 🙂

  12. August 12, 2012 at 8:09 pm

    Hello Richard Good day. I have a doubt and would like your help. I have a Microsoft Forefront TMG Firewall 2010 SP2 that uses SQL Server 2008 Express. I have an application that is Microsoft PowerPivot that accesses the database and the TMG must be allowed access to the bank, but I can not in any way. Anyone know which permission the SQL Server must provide in order to access this information or will be problems that could be a rule in the firewall. Recalling that the application will access Microsoft PowerPivot inside a workstation to the DataBase TMG Firewall


  13. August 22, 2012 at 9:51 am

    By default, the SQL instance installed on the Forefront TMG 2010 firewall is not accessible remotely. You’ll have to install the SQL tools on TMG itself and then enable TCP connectivity for the database. After you create the appropriate access rule to allow the traffic, you should be able to read data from the database remotely (providing you have sufficient administrative rights to do so).

  14. john
    January 8, 2013 at 5:47 am

    Good Morning, I have ran the script multiple times, but it appears to never “stick.” When I check the memory usage 24 hours later, the SQL service is still using 1.5+ GB.

    Any ideas, would be appreciated.
    Thank you!

  15. January 12, 2013 at 7:42 am

    I don’t recall if restarting the SQL service is required, but if you haven’t done so already, I’d give that a try and see what happens.

  16. Ahmad Alito
    April 10, 2013 at 5:56 am

    I have SQL Server 2008 and TMG 2010 SP2 when I ran the script. I got the following message:
    C:\Windows\system32>osql -E -S .\msfw -i c:\showsqlmem.sql
    Microsoft (R) SQL Server Command Line Tool
    Version 10.0.1600.22 NT x64
    Copyright (c) Microsoft Corporation. All rights reserved.

    Note: osql does not support all features of SQL Server 2008.
    Use sqlcmd instead. See SQL Server Books Online for details.

    usage: osql [-U login id] [-P password]
    [-S server] [-H hostname] [-E trusted connection]
    [-d use database name] [-l login timeout] [-t query timeout]
    [-h headers] [-s colseparator] [-w columnwidth]
    [-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
    [-L list servers] [-c cmdend] [-D ODBC DSN name]
    [-q “cmdline query”] [-Q “cmdline query” and exit]
    [-n remove numbering] [-m errorlevel]
    [-r msgs to stderr] [-V severitylevel]
    [-i inputfile] [-o outputfile]
    [-p print statistics] [-b On error batch abort]
    [-X[1] disable commands [and exit with warning]]
    [-O use Old ISQL behavior disables the following]
    batch processing
    Auto console width scaling
    Wide messages
    default errorlevel is -1 vs 1
    [-? show syntax summary]


    What’s the problem.

  17. Ahmad Alito
    April 10, 2013 at 6:11 am

    I’ve used sqlcmd instead of osql but I’ve got the following results:

    C:\Windows\system32>sqlcmd -E -S .\msfw -i c:\showsqlmem.sql
    HResult 0x102, Level 16, State 1
    Shared Memory Provider: Timeout error [258].
    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Unable to complete logi
    n process due to delay in prelogin response.
    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.


    Please advice.

  18. April 10, 2013 at 7:34 am

    Are you running these command from an elevated command prompt? And are you logged on with full TMG administrative privileges?

  19. Ahmad Alito
    April 10, 2013 at 10:20 pm

    Yes, I ran the command as adminstrator and I’m logging in to TMG with domain admin account.

  20. Ahmad Alito
    April 11, 2013 at 5:35 am

    I have restarted the TMG server and after that I was able to run the command and it seems working fine and the problem solved.

    Thanks a lot Richard.

  1. February 8, 2012 at 4:57 pm
Comments are closed.