Zedan IT Blog

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%'”

tmgimg1I 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
go
drop database ISALOG_20120527_WEB_000
go
….
….
….

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

tmgimg2

4)  Restarted the “Microsoft Forefront TMG Firewallservice 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”.

tmgimg3In my next post I will explain a VB script I wrote to import entries from summary logging database and export them to a single SQL database to analyse them.

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s