When you delete an attachment in Ideagen Internal Audit (Aura), all that is removed from the database is the link between it and its parent record.  The file itself remains intact, albeit unavailable from within the application.

Over time this can lead to an accumulation of unnecessary files in the database, so there is a 'purge' job that can be run on a regular basis to remove them.  This is not activated by default.

This article describes the process for activating the job.

Finding the data volume of orphaned files

Optionally, before activating the job you can assess how much space will be freed by purging.  This query will report the total volume in Mb:

-- Display the total size of orphaned files in the filestore (in Mb)
SELECT (SUM(CAST(FileSize AS BIGINT)))/1048576 FROM FileStore
WHERE
FileID NOT IN (SELECT FileUri FROM DocumentInfo) AND
FileID NOT IN (SELECT FileUri FROM TemplateInfo);

This is not a mandatory step.

Activating the job

The PurgeFileStore robot job can be activated as follows:

• Launch App Manager, and ensure that you have selected the correct Pentana instance
• Click the [Robot] tab
• Click [Robot Service] > Connect and wait for the connection
• Scroll down the Robot Jobs list to the one with a Name of PurgeFileStore
• Check the checkbox in the Active column and click Save
• Restart the Pentana Robot service

The job will now be active.  Note that depending on the number of files to be deleted, the Robot may not clear the entire set of orphaned files on its first run.  It may take multiple runs to clear.

A note on scheduling

By default the Robot will run the purge job daily at 04:00, as specified in the Cron Value field.  This can be changed to a more suitable time if required, but ensure that active jobs are not scheduled to start at the same time.

A potential issue - timeout during the first purge

If there is a large volume of file data to be purged, then the initial run might timeout.  In that case, an initial purge can be performed directly in the database.  See Ideagen Internal Audit (Aura) - Purge FileStore job times out after being enabled – Ideagen Internal Audit (Aura) (ideagen.com).  Thereafter, the Robot should manage a scheduled purge.

Shrinking the SQL Server data file

Note that purging the deleted files will free up space within the database and make it available for reallocation, but will not reduce the size of the SQL Server data file itself.  This is a database management task that will need to be carried out by the database administrator.

References

Cronmaker:  http://www.cronmaker.com/?1