My audit trail is making my database too big (1 Viewer)

[FISHiEE]

Hi,

I have discovered a problem with my database and wonder if anyone here can point me to the best solution...

The problem is this... when I took our company database over about 3 years ago it was around 45MB... now it is almost 350MB and growing steadily.

I have today discovered the main cause of this... the audit trails on a couple of the tables. If I remove these 2 fields only from the database it cuts it down to under 100MB. The Audit trail is quite important for us and so I'd like to keep it if I can. It is stored in a memo data type (the data is well over the 255 char limit of a text box). Is there any other better way to store this data or is there anything I can do to reduce the size of the memo data type?

Any advice would be greatly appreciated!

 

[neileg]

Create your audit trail in a separate mdb file.

 

[The_Doc_Man]

At some point the audit, while still valuable, becomes strictly of limited use.

Generate a report of all audit entries older than X then export those entries to a file with comma-delimited fields and text fields in quotes. Then delete entries older than X.

Then compact and repair the DB.

If you make a duplicate of this database, complete with audit tables, and ERASE the tables but leave their structure intact, you can import that old audit data any time you need to for analysis.