Welcome to ISAserver.org

SQL Logging - Database file size concerns

Users viewing this topic: none

Logged in as: Guest

Printable Version

All Forums >> [ISA Server 2004 Firewall] >> Logging and Reporting >> SQL Logging - Database file size concerns

Page: [1]

Message

<< Older Topic Newer Topic >>

SQL Logging - Database file size concerns - 1.Nov.2006 10:20:13 AM

waynewhittle

Posts: 96
Joined: 21.Apr.2004
From: Cardiff
Status: offline

Is it possible to configure SQL to overwrite entries older than say a month old. I can see the size of the database just growing and growing and wondered what the best way was to dynamically keep it in check. We only require data going back a month or so for reporting. I reconfigured logging to a remote SQL server about three hours ago and the database size is 350MB already!!

Also since there are only about half a dozen fields I actually require to be logged in the database is it possible to configure this. In the Configure Web Proxy Logging if I select just a few fields it appears to be ignored - the SQL Web Log database still seems to log on every field...

Post #: 1

Featured Links*

RE: SQL Logging - Database file size concerns - 6.Nov.2006 7:03:29 PM

Posts: 12
Joined: 16.Mar.2006
Status: offline

What you can do is create an SQL agent job that removes old log entries. I created a job with 3 steps.

Step one Truncate Log File
BACKUP LOG ISA WITH TRUNCATE_ONLY

Step two Remove old records
Delete from WebProxyLog where (CURRENT_TIMESTAMP - Logdate) > 30

Step three Truncate Log File
BACKUP LOG ISA WITH TRUNCATE_ONLY

The delete statement writes everything to the log first so I found the log file filling and the job would fail. The Backup Log Truncate_Only statements just ensure there was maximum space available in the log file.

In my environment I found the ISA DB, with WebProxy records only, would grow to about 15GB if I kept records for 30 days.

I have tried using the Trim method, suggested on this site, daily to remove free space but still found the DB growing by about 500MB a day.

(in reply to waynewhittle)

Post #: 2