IT definitions, discussions and more
TMG Reports Empty & Corrupted SQL Logging
I had a problem annoyed me for about two years tat when generating reports in TMG, they were always completed with empty data and graphs and after trying to solve the problem by searching in internet I found (by chance) that reports will only work if logging in TMG is configured to log to SQL database not text log files.
It was strange and annoying for me as that was not the case when I used ISA 2004 & 2006 before. I always configured ISA logging to text log files so I can use them with other log analysis tools.
So, I have changed the logging back to SQL server (internal SQL Express server) , but surprisingly reports were completed empty as well 😦 . While searching again on the internet for days I found a solution steps to correct problems when logging to SQL server. My problem was similar that it seemed that there was a corruption in the SQL Summary database when I was looking after the problem before and bounce between test and database logging. The original post from Forefront TMG Product Team Blog: “TMG Logging to LLQ“
The solution to my problem was:
1) Listing the databases currently registered in SQL Express server. From elevated command prompt:
OSQL -E -S .\MSFW -Q “select name from sysdatabases where name like ‘%isalog%'”
I found that there are number of databases registered over a year ago, but they are physically not on my server. As you see in the result of the command, the system cannot find the database files associated with registered database, as explained in the error: “<The system cannot find the file specified.>”.
2) Then I have dropped all databases by writing command of drop for all databases, then saved them into a text file:
drop database ISALOG_20120527_FWS_000
drop database ISALOG_20120527_WEB_000
3) Then saved the file as text file named DropDB.sql, then at the elevated command prompt executed the command:
OSQL -E -S .\MSFW -i c:\DropDB.sql
4) Restarted the “Microsoft Forefront TMG Firewall” service and then check back the Log Status. Then clicked refresh a few times till current status no longer as “Disconnected” but as “Queue in use”.