Making AuditTable smaller

select gn_Action, COUNT(*) as 'Number' from gn_AuditTable group by gn_Action order by Number desc

The above query lists number of records in the gn_AuditTable by type (see Audit trail actions).

A possible result:

13        9370792

3        2170935

16        2101742

15        2101553

2        1863419

6        790733

10        55164

0        11348

1        8452

21        8301

19        5266

4        915

5        678

11        152

9        7

12        2

Knowing the audit trail codes, the result becomes more readable, and you can see that searches take more than 50% or records.

* triggered by the modification of another object that influences the value of automatic computed attributes.

** of an object referenced by this one or by an un-spike operation of the object that references non-existing other objects

The presence of the search entries means that the Audit Searches flag (see System permissions) is enabled for an user or groups and it is logging all the searches. Such logging records all the parameters of every search, that's why it creates many large records.

Auditing of updates is by default enabled also on configuration items, therefore, the table continues to  grow. You can analyze the audit table by object id, for the action update (3) with the following query (all code in one line):

select gn_ObjectId, COUNT(*) as 'Number' from gn_AuditTable where gn_Action = 3 

group by gn_ObjectId order by Number DESC

You can get something like this:

The first object in the list has been updated more than 280,000  times. The object is a configuration object called auditWPParams_editions_gnpc_Audit.

The second one is a dataSrc object that I use to download content from remote FTP locations (download files to the local server). The third one is another dataSrc object that downloads AP stories.

This is known problem and Tera is working on a solution.

IMPORTANT: DELETING ITEMS FROM THE AUDITTABLE OR OTHER DATABASE TABLES MAY BREAK YOUR GN4 SYSTEM! DO NOT DELETE ANYTHING WITHOUT CONSULTING TERA SUPPORT FIRST!

MAKE A FRESH BACKUP OF THE DATABASE BEFORE DELETING ANY ACTION!

The only actions that may be safely deleted are 0 (login), 1 (logout) and 13 (search).

Should you want to get rid of some audit records, e.g. AuditSearch, disable AuditSearch for groups and users, and then delete them from the database, by using the query:

delete gn_AuditTable where gn_Action=13

Take into account that the deletion may take some time. Typically, the deletion speed may be considered as 10-11 thousands row by second, therefore, about 14 minutes to delete +9 million or records.

Moreover, truncate the gn_AuditSearchTable, e.g.

truncate table gn_ AuditSearchTable

After all these deletions, check the size of SQL log that may increase pretty much: consider to make a backup.

In a real-life example, the database file that was 38Gb was shrunk to 18 Gb, therefore, 20Gb less. Database dump was 58Gb instead of 78Gb before these operations.

Deleting old items with the action 3

Since build 2.1, you can delete also the old items with the action 3, e.g. the items older that 30 days. Use a custom query to delete such items. Make a fresh backup of the database, or - even better - try first on a test database. Do not delete all the items with the action 3!