We often recommend customers using Microsoft ISA or TMG switch their logging to W3C text file, in order to get the best possible import speed, and also because the text logs are much easier to access from a remote machine (see my previous article on accessing TMG’s SQL Express Log database). Logging to the default MSDE or SQL Express databases also requires more resources in terms of processor utilization, memory consumption and disk I/O.
But there is another advantage to switching to text. They take up considerably less disk space. Here are some figures:
Number of Records in 235 MBs of log data:
235 MB of TMG’s W3C text logs contains 326,824 records. An SQL Express database of the same size (mdf and ldf files) contains only 40,308 records. In other words, w3C text logs can store over 8 times as much data in the same amount of disk space.
A rule of thumb:
By switching to W3C text logs, the disk space taken by your log files will be roughly 12% of the SQL Express or MSDE log files. This can be reduced even further by compressing your text logs.
- MSDE/SQL logs: budget for 5 KB per record
- W3C Text logs: budget for 0.71 KB per record
How many records your ISA or TMG server creates per day will depend on the number of users in your organization and how much traffic they generate, but about 16,000 records per user is a reasonable estimate.
A real world example
If you are hitting 500 GB of SQL Express/ MSDE logs per month (about 86,128,205 records), simply switching to W3C text logs will reduce this down to 61 GB.
Once imported into a WebSpy Storage, the storage size would be roughly 53 GB (87% of the original W3C text logs).
With NTFS compression applied to the Storage folder, the WebSpy Storage would be roughly 13.4 GB (22% of the original W3C text logs).
Applying NTFS compression to your WebSpy Storages folder is certainly a good idea. This does not impact performance. If anything, it may improve performance slightly as there is less disk fragmentation within the storage.
Disadvantages and Alternatives
Please be aware that by changing your logging to text, the default reporting functionality within TMG will no longer work. However, the reporting supplied by WebSpy Vantage should more than adequately replace this feature.
If you are still concerned about changing the logging method, you can utilize a script published by Microsoft to convert your SQL Express logs to W3C text. You can then keep the text logs and set some more stringent data retention policies on the SQL Express logs, such as clearing logs every week. You can download this script as part of the Microsoft Forefront Threat Management Gateway (TMG) 2010 Tools & Software Development Kit.
- Here’s a great article by Marc Grote at isaserver.org on the pros and cons of the different logging options in ISA and TMG. It also takes you through how to exclude fields to reduce the amount of data being logged:
- Also take a look at Richard Hicks’ blog regarding MSDE performance with ISA Server 2006:
- Here’s another article on isaserver.org by Richard Hicks on the logging enhancements in TMG 2010
The figures above were produced using some sample logs received from customers with similar (but not exactly the same) logging settings. If you have changed to text logging, I’d be very interested to hear the sort of disk savings you are seeing, and I’m sure others would to. So please leave a comment below.