Frends uses SQL Server for storing the configuration and log data. The databases need to be periodically maintained.Databases are created and migrated to the newest version with the Frends.DatabaseInitializer tool, which is automatically executed by the deployment scripts. To get a full list of parameters, execute it with the '--help' parameter. By default the databases are created with the simple backup recovery model.
To prevent the database size from growing uncontrollably, Frends Log Service deletes old Process Instances from the database periodically. By default, any instances older than 60 days will be removed, but you can change the settings for a specific Agent Group (Environment before frends 5.4) or Process. See Process Log Settings for more. The purge is done by executing the stored procedure 'PurgeProcessHistory'. The purge procedure has a 30 minute timeout, if it cannot finish or an error occurs, the execution is retried after 30 minutes.
After purging old Process Instances successfully, the Log Service will reorganize indexes that have reached at least 30% fragmentation, each index reorganize has a 30 minute timeout.
By default, the Process Instance purge and index reorganization will be run on Log Service startup, and is rescheduled to run every 24 hours after finishing successfully. The maintenance actions will run for 30 minutes max; if the actions time out, or there is some other error, they will be retried 5 times by default.
You can configure the maintenance actions with the following optional settings in deploymentSettings.json. These settings should be put directly under the root settings node:
maintenanceTimeWindowStart - string with a format of "[hour]:[minute]:[second]", e.g. 00:30:00 for half past midnight
maintenanceRetryCount - number
disableDatabaseMaintenance - boolean, set to true if you have set up your own scheduled cleanup and maintenance procedures.
Determine which Process is filling the log store table
When the Frends database is huge, probably there are probably some Processes that have are exceeding log levels, so the Process will generate a lot of logs. Another thing worth looking at is that log retention time.
Additional steps in Self-Hosted installation
It is possible to investigate whether the strain cleaning procedure has been run. From Frends Message Service logs it is possible to see if PurgeProcessHistory is run without mistakes. Logs can be found here: ‘Program Files (x86)\HiQ Finland\FRENDS Log Processor\logs’
In addition, following query can be used to check see which Process / step uses most disk space.
SELECT COUNT(*) TotalCount,
sum(datalength(a.RESULT)) as "Datalength",
c.IDENTIFIER,
d.NAME
FROM PROCESS_INSTANCE_STEP a
INNER JOIN PROCESS_INSTANCE b
ON a.PROCESS_INSTANCE_ID = b.ID
INNER JOIN PROCESS c
ON b.PROCESS_ID = c.ID
INNER JOIN AGENT_GROUP d
on b.AGENT_GROUP_ID = d.ID
INNER JOIN PROCESS_STEP e
on a.PROCESS_STEP_ID = e.ID
GROUP BY c.IDENTIFIER, d.NAME