Freigeben über

TMG Logging to LLQ

  • Artikel

One of the problems causing TMG to log to LLQ instead of the database is the presence of orphaned databases in the local SQL Server instance.
In other words you may have some databases that are registered on the local SQL Server but the corresponding .mdf and .ldf files are missing from the disk. This may occur if the files were manually deleted, the volume with the files are no longer available or for other reasons.

It’s also important to note that this may happen when logging is configured for either the local database or remote SQL database. This is because TMG still checks the health of the local instance even if you log to a remote database.

When this problem occurs you may see something like this:

image

To understand if this is the case you will need to check the logs of the local SQL Server instance that are located by default in C:\Program Files\Microsoft SQL Server\MSSQL10.MSFW\MSSQL\Log while the databases themselves are by default in 'C:\Program Files\Microsoft Forefront Threat Management Gateway\Logs\.

Open the file ERRORLOG from the logs folder and search for lines that looks like the following:

2012-09-05 10:44:52.01 spid54 Starting up database 'ISALOG_20120831_FWS_000'.
2012-09-05 10:44:52.02 spid54 Error: 17204, Severity: 16, State: 1.
2012-09-05 10:44:52.02 spid54 FCB::Open failed: Could not open file C:\Program Files\Microsoft Forefront Threat Management Gateway\Logs\ISALOG_20120831_FWS_000.mdf for file number 1. OS error: 2(failed to retrieve text for this error. Reason: 15100).
2012-09-05 10:44:52.15 spid54 Error: 17207, Severity: 16, State: 1.
2012-09-05 10:44:52.15 spid54 FileMgr::StartLogFiles: Operating system error 2(failed to retrieve text for this error. Reason: 15105) occurred while creating or opening file 'C:\Program Files\Microsoft Forefront Threat Management Gateway\Logs\ISALOG_20120831_FWS_000.ldf'. Diagnose and correct the operating system error, and retry the operation.

Next thing you should check is what happened to the missing files.

If you changed the log location to another volume and this volume is currently unavailable then try to get the volume back online if possible.

If there is no way to get those files back you should proceed by removing the database registrations from the local master database. You can identify the names of the orphaned databases by running this command at an elevated command prompt:

OSQL -E -S .\MSFW -Q "select name from sysdatabases where name like '%isalog%'"

Compare the names from the output of the above command with the database files in the log file directory. Once you have identified the names of the missing databases you should prepare a text file with the commands to drop each missing database. It should look something like this:

drop database ISALOG_20120831_FWS_000
go
drop database ISALOG_20120831_WEB_000
go
drop database ISALOG_20120901_FWS_000
go
drop database ISALOG_20120901_WEB_000
go

Save this file, for example, as c:\DropDB.sql

Then from an elevated command prompt execute this command:

OSQL -E -S .\MSFW -i c:\DropDB.sql

Now restart the “Microsoft Forefront TMG Firewall” service and then check back the Log Status, you should see the current status no longer as “Disconnected” but as “Queue in use”. If you click Refresh you should also see the LogQueue(KB) decreasing.

image

Depending on how long the issues lasted and the amount of data being logged by your server, it may take few minutes or a few days for the queue data to be processed.

Once this operation completes you should see the status as Ready again.

Author:
Gianni Bragante
Support Engineer - Microsoft CSS Forefront Security Edge Team

Reviewer:
Lars Bentzen
Escalation Engineer - Microsoft CSS Forefront Security Edge Team

Comments

  • Anonymous
    January 01, 2003
    seems its working for me also.. but have to wait untill finish this.

  • Anonymous
    January 01, 2003
    seems its working for me also.. but have to wait untill finish this.

  • Anonymous
    January 01, 2003
    Also don't forget to check SECURITY rights for the log folder and volume. Mus be:
    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

  • Anonymous
    September 19, 2012
    It work, thank you so much. I try many times to find out the problem. However, I rename another file to the file sql could not find instead. It working too.

  • Anonymous
    October 23, 2012
    Hi ther, I don't have any database at sql sysdatabse with the forefront name, but i know from the logs that SQL is trying to open a databse with isalog name but it can't....

  • Anonymous
    February 22, 2013
    Thanks, it worked for me too. I had an issue when i tried to move the log directory and changed it again. Upon restart SQL was looking for logs in the old drive i had setup. Once i found the problematic file in SQL logs, i issued the drop command and now everything is back to normal

  • Anonymous
    January 26, 2014
    Thanks a lot, this helped me to solve a problem with no solution for over a year.

  • Anonymous
    March 24, 2015
    Great. It solved my problem with TMG Lockdown Mode

  • Anonymous
    July 20, 2015
    GREAT!!!! It really solves the issue!

  • Anonymous
    September 02, 2015
    nice post