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:
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.
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
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.
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....
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
seems its working for me also.. but have to wait untill finish this.
Thanks a lot, this helped me to solve a problem with no solution for over a year.
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