Cleanup File Change Log Database for Triofox

Triofox database (named 'csmain' by default) contains two types of information.

The first type of information is relatively static. For example, username, user email, user’s published shared files and folders, etc. This type of information is relatively small. It can be estimated at 5000 bytes per user. So if you have 2000 users, it can be estimated at 10 MB.

The second type of information is relatively more volatile. For example, the File Change Log, the Audit Trace, and the File List. It can be estimated at 100 file changes per user per day and each file change can be estimated at 1000 bytes for database storage. So for a 15 day (Default Change Log history), it can be estimated at 100*1000*15 = 1.5MB per user for a 15 day period. If you need HIPPA compliance, the default Change Log History can be significantly longer so this part of the database will be much bigger.

For performance and regulatory reasons, it is recommended to split the database into two separate databases, one that contains static information and one that contains relatively more volatile information.

If you are using SQL Express as the Triofox database (Triofox Server installed before May 21st, 2018 version 9.5.5409.41172), the maximum size the database can grow to is 10GB. There are times due to many file changes, this limit will be reached after which there will be issues adding more users to the Triofox platform, etc.

In such cases, there are 3 options to clean up the file change log database or you can just migrate to MySQL/PostgreSQL (installed by default after May 21st, 2018 version 9.5.5409.41172).

1. Configure Retention Policy

This option will clean up the file change log database automatically. You can configure the 'Keep file change log for n days' setting under Management Console \ Default Group Policy \ Retention Policy to a smaller number of days for example 10 or 15 (15 is the default) which will clean up the file change log database automatically where it will delete the file changes which are older than for example 15 days. This will keep file change log database size in check

Enable Storage Scan Setting:

The changes done above will start clearing the changelog database when the storage scan takes place which is every 24 hours. In order to start storage scan right away, you will need to enable the storage scan setting under Cluster Manager \ Cluster Control Panel \ Worker Nodes and click on the 'Edit Cloud Monitor Settings' button for the worker node in the last column

2. Purge old Database Records

Using the Cluster Admin credentials, go to the System Info/ Reports / Node Performance. Here in the Database Report, the Admin has the option to 'Purge' File Change Log, Audit Trace, and File Index

3. Purge the Records directly from the Database

There is a table xaf_filechangelog, which probably is the biggest table, and all tenant's file changelogs are in it.

Other Database tools if your database is not SQLExpress

• PostgreSQL: pgadmin 4
• MySQL: MySQL workbench

You can take entries with older timestamps and archive them into a different place or delete them and free up space in this table (and thus free up space in this DB).

You can also check to see if there are tenants setting it to longer than 30 days by looking into the xaf_namedvalue table and the name is "FileChangeLogDays@2".

There are times when the system OS drive will be full due to file change logging database growing too much and SQL error logs can also fill up the system drive, for example, C: drive. (This is mostly the case when using SQL Express).

Since the OS drive is full, make some room there first. Assume the SQL error log is on C drive; shut down SQL Express. Then move/delete all error logs to make some room on the C drive.

Now, that your xaf_filechangelog table is already full need to delete some data there. You can run SQL statements like delete from xaf_filechangelog where timestamp < 'timestamp 15 or 20 ago'.

It will delete all the old file changelogs.