Cleanup File Change Log Database

CentreStack database (named 'user' 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.

Take a look at 'Advanced Topics' section in the Deployment Guide to see the instructions on how to split the database.

http://centrestack.com/Library/DeploymentGuide/chapter11.html

If you are using SQL Express as the CentreStack database (CentreStack 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 CentreStack platform etc.

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

1. Migrate the SQL Express Database to MySQL Database

Find in this article how to achieve that: Migrate All-In-One Microsoft SQL Express Database to All-In-One MySQL Database

2. 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

This Policy can be changed at Tenant Admin level, Management Console \ Group Policy \ Retention Policy

Enable Storage Scan Setting:

The changes done above will start clearing the change log 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 storage scan setting under Cluster Manager \ Cluster Control Panel \ Worker Nodes and click on 'Edit Cloud Monitor Settings' button for the worker node in the last column

3. Purge old Database Records

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

4. Purge the Records directly from the Database

There is a table xaf_filechangelog, that probably is the biggest table and all tenant's file change logs are in it.

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 you full need to delete some data there. You can run SQL statement like delete from xaf_filechangelog where timestamp < 'timestamp 15 or 20 ago'.

It will delete all the old file change logs.