Configuring SQL Memory Limits on Forefront TMG 2010
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:
Connect
Richard Hicks' Forefront TMG Blog 
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
Tnx for this, I’ve been looking for this.
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,
You are using the wrong slash. It should be .\msfw and not ./msfw. 🙂
Thank you Richard 🙂
It works great!
Regards,
Richard,
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,
Colin
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.
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?
thanks
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.
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.
regards
Good to know. Thanks for sharing! 🙂
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
Thanks
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).
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!
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.
Hi,
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]
C:\Windows\system32>
What’s the problem.
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.
C:\Windows\system32>
Please advice.
Are you running these command from an elevated command prompt? And are you logged on with full TMG administrative privileges?
Yes, I ran the command as adminstrator and I’m logging in to TMG with domain admin account.
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.