I've recently set our ISA server to log to a SQL database. This is working great, except the database size is growing about 1 gig every day! I've found many posts that suggest doing a backup, then run a truncate table command.
I'd like to be able to provide web usage reports for 1-2 months back. If I start archiving the database, this will make reporting a huge hassle. Any help, input or suggestions would be greatly appreciated!
make sure the SQL database recovery mode is set to simple. (is the majority of the growth in the LOG file or the DATA file of the SQL DB?) Alternatively, consider logging to the included MSDE, which rolls the database file each day, which you may find more managable?
I had the same problem. Thing is that you cannot filter what is inserted into the database from ISA server level.
Besides using simple recovery mode, another workaround is to filter what is inserted into the DB by using SQL triggers. You have to think about what kind of report you would like to have? There is no way you'll need all the data, even if you'll do reporting for a large enterprise. Base on that you can apply several triggers to the same table, or even a trigger which may include all conditions. Beware though, the more triggers set for INSERT on the same table, the higher SQL performance hit will be. So, play smart. Also consider your SQL machine's muscles...
Triggers are a good way of filtering for example noise traffic, such as ARP requests or other common infrastructure protocols.
Here is an example of a SQL trigger:
create trigger ISAFilter on [table name] for insert asif exists (select 1 from inserted where [columnname] <> <OK value>) begin rollback end