Relocating SQL Database Files on Forefront TMG 2010
When Forefront Threat Management Gateway (TMG) 2010 is installed, an instance of SQL Server 2008 Express is included for Forefront TMG firewall and web proxy logging. By default, the log database files are installed on the system partition, which is less than ideal. Best practices dictate that log database files should reside on a separate, dedicated partition.
I’ve had many people ask how to move these database files once the product is installed. Most assume that the process involves using SQL database management tools to detach the database and manually move the database files to a new partition. Not true! Since Forefront TMG handles all of the underlying SQL database management, the process is actually quite simple.
To move the log database files, first create a folder to store them in the new location. Next, open the Forefront TMG management console, highlight Logs & Reports in the navigation tree, select the Logging tab in the center console window, then click Configure Firewall Logging in the Tasks pane on the right.
Click the Options… button, then select This folder (enter the full path): and enter the new path to store the log database files.
For EMS-managed or standalone arrays, make certain this path exists on each array member. If it does not, the service will not start. If the folder does not exist, TMG will complain.
Repeat this process to move the web proxy log database files. In addition, it would be an excellent idea to also move the Log Queue Storage Folder. This folder should be located on a partition that is separate from the one used to store the log database files. For optimum availability this will be a separate physical disk, allowing for Forefront TMG to continue logging to the queue even in the event of a physical disk failure where the log database files are stored. As with the log database files, this folder must exist on each array member.
A system variable can be used to specify the path to log database or log queue files. For example, %LOGDRIVE%\FWS, where %LOGDRIVE% can be a different drive letter and path on each array member, if necessary. To create a system variable, open the advanced system properties and click Environment Variables….
Under System variables click New…, enter the variable name (e.g. LOGDRIVE), and specify the location where the log files should be stored on this array member (e.g. D:\TMGLogs). Repeat these steps on each array member, specifying the local path where log database files are to be stored.
Confirm the system variable was created properly by opening a command prompt and entering the following command:
set logdrive
The output for our example should appear as follows:
LOGDRIVE=D:\TMGLogs
Connect
Richard Hicks' Forefront TMG Blog 
Be aware, for any alternative logging folder, the Network Service account must have read permissions from the root partition and any parent folder for the folder. On the logging folder itself, the following permissions are required:
• Network Service: Full Control
• System: Full Control
• Administrators: Full Control
For more information on alternative logging folder please check http://technet.microsoft.com/en-us/library/cc995083.aspx
Best regards,
Thomas Rafetseder
Excellent point. Thank you for the additional information!
Perhaps this is an obvious question, but will changing the log path via the TMG management console also relocate existing log files?
You’ll have to create the folders for the log files first, but yes, changing the log path using the Forefront TMG management console is exactly what I’m describing in this article.
I think I some how screwed up this whole guideline by “accidently” (read user moronity) deleting the temdb and all the other logfiles first because they where eating up my H: drive.
I followed the above steps and changed the path to my D: drive, but now the system want start the sqlinstance anymore, and comes with the following error:
FCB::Open failed: Could not open file H:\LOGS\Firewall_LOGS\tempdb.mdf for file number 1. OS error: 3(The system cannot find the path specified.).
Does some one have an idea on how to fix this again?
Regards
That’s not good! 😉 I’d suggest trying to repair the installation using your original media. If that doesn’t work, export your configuration and perform a complete uninstall/reinstall.
Is it possible to relocate the log files to a separate network server using this method? If so, would it cause constant traffic from the TMG server to the storage server? Or would the log file only get sent across the network every so often?
It is possible to move log files off of the Forefront TMG 2010 firewall, but not using the method described in this article. You can configure Forefront TMG to use an external SQL server, or you can use an agent like Splunk that I blogged about here.
Changing the path within TMG does not move any existing database log files.
New files get created in the new location but all existing ones stay in the old location and are still open databases within SQL Express.
They might end up being removed after the loggin period expires but I won’t know until then.
I may have to try modifying the database file paths within SQL itself to move them before then.
You are correct. Updating the log file location in the Forefront TMG 2010 management console won’t move any existing log files. You’ll have to move those yourself manually.
Hi folks, might be a silly question.. but:
I had also had to change the log file location, and as mentioned, new log files are being created there. But I can’t get rid of the old ones. I’m getting the message that the files are still open in SQL… but no SQL management studio on the TMG server.. so I’m a bit lost.. any hints are much appreciated!
Cheers,
Cloudy
They are most likely being used for reporting purposes. You might be able to delete them by shortening your log file retention time, however.
That’s the ‘Delete files older than (days)’ option, right?
Correct. 🙂
OK, i tried that but didn’t work.. 😦
So I ended up stopping the SQL Service and then i was able to remove them.
Thanks for your help!!
I runned in to the same problem with no able to deleting the old logs after changing the log path.
I solved the problem by detacht the old log files with sqlcmd, and then you can move/delete them.
To list all attachted log files/dbs From C:\Program Files\Microsoft SQL Server\100\Tools\Binn folder run sqlcmd -S localhost\msfw -q “select @@version”
1> select @@version;
2> go
1> select name from sys.databases;
2> go
You will now have a list of all attachted mdf/ldf
To deattach run:
exec sp_detach_db ‘ISALOG_20130625_WEB_000’, ‘true’;
This will deattach both the mdf and ldf connected to ‘ISALOG_20130625_WEB_000’.
Continue with the other old WEB and FWS logs/dbs.
I have the following problem. TMG stopped deleting older logs automatically and my HD is almost no disk space. How can I fix this problem? Also would like to know how do I delete old logs manually.
Thank you.
I’d suggest reviewing your Forefront TMG 2010 log settings to make sure that you have the correct settings enabled for TMG to delete log files on a regular basis.
http://technet.microsoft.com/en-us/library/bb794937.aspx