Home > Forefront TMG 2010 > Relocating SQL Database Files on Forefront TMG 2010

Relocating SQL Database Files on Forefront TMG 2010

April 11, 2011

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:


Categories: Forefront TMG 2010
  1. April 11, 2011 at 10:20 pm

    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

  2. April 12, 2011 at 7:27 am

    Excellent point. Thank you for the additional information!

  3. John Forrister
    May 11, 2011 at 7:23 am

    Perhaps this is an obvious question, but will changing the log path via the TMG management console also relocate existing log files?

  4. May 11, 2011 at 3:21 pm

    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.

  5. Danny
    September 30, 2011 at 1:59 am

    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?


  6. September 30, 2011 at 1:27 pm

    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.

  7. October 26, 2011 at 6:33 am

    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?

  8. October 28, 2011 at 9:33 am

    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.

  9. Grant
    November 22, 2011 at 3:45 pm

    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.

  10. November 22, 2011 at 9:54 pm

    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.

  11. Cloudy
    August 22, 2012 at 7:09 am

    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!


  12. August 22, 2012 at 9:52 am

    They are most likely being used for reporting purposes. You might be able to delete them by shortening your log file retention time, however.

  13. Cloudy
    August 23, 2012 at 1:02 am

    That’s the ‘Delete files older than (days)’ option, right?

  14. August 29, 2012 at 1:12 pm

    Correct. 🙂

  15. Cloudy
    August 30, 2012 at 12:40 am

    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!!

  16. JoHEX666
    July 11, 2013 at 4:13 am

    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.

  17. July 18, 2013 at 10:18 am

    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.

  18. July 22, 2013 at 1:21 pm

    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.


  1. No trackbacks yet.
Comments are closed.