Our customers' databases vary widely in size, depending on the number of users, the age of the system and how the organisation works with file attachments.  As any of these increase so the size of the database increases, and as audit data is usually not deleted there is little scope for reclaiming space.

However, there are some things to consider in order to ensure that the database is only as large as it needs to be:

• Is the SQL server recovery model suitable for the backup strategy?  A mismatch can cause the log to inflate unnecessarily
• Are there orphaned/deleted attachments still in the system?
• Is there a lot of unnecessary audit trail data?

These issues are discussed in more detail below.

The SQL Server log file seems too large

This article is not a guide to SQL Server and should not be considered advice or recommendation, but includes information you may find useful.  You should consider the appropriateness of the content in conjunction with other resources and guidance.

It is difficult to say how large a database log file should be, as there are many contributing factors.  DBA's often estimate that a size of 20-25% of that of the data file is reasonable, and in fact we find that Ideagen Internal Audit (Aura) database logs are often much smaller than that.

If your database log file is significantly larger than expected then it can point to a mismatch between your database's recovery model and how backups are being taken on your server.

We ship our starter database in FULL recovery mode, so SQL Server will continue to add to the log file until a log backup is taken.  If you are not making transaction log backups then the file will just get larger over time.

You have two options to consider:

• Keep the existing FULL model and add transaction log backup to your backup strategy
• Change the database recovery model to SIMPLE, and the log file will not continuously grow

This decision affects the point in time you can restore the database to if there is a problem, so requires discussion between the DBA and the users.

'Deleted' attachments are not truly deleted

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 remains present in the FilesStore.

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.

The procedure for assessing the likely space saving and activating this job is described in this article: Ideagen Internal Audit (Aura): Assessing the volume of deleted files and purging the FileStore.

Note that in a system already containing a lot of data the initial purge can timeout - a solution for this is described here:  Ideagen Internal Audit (Aura) - Purge FileStore job times out after being enabled

Old audit trail records are not deleted by default

Where the audit trail is enabled, audit records will be continually added to the trail.  You may decide that the retention of old audit trail records is unnecessary and choose to purge them after a time.

There is a set of robot jobs which can be configured to remove audit trail records older than n months.

The procedure for activating these jobs is described in this article:  Ideagen Internal Audit (Aura): Purging the Audit Trail.