How to: Delete events from the log table without creating a big transaction file (16.2 and newer versions)

Applies to:

Venafi Trust Protection Platform 16.2 and newer

Symptom:

When performing the log expiration command, a simple delete statement against the log table is performed. This could be inefficient if you already have a high number of events logged.

Resolution:

By splitting the delete job into several small batches, the transaction log file will not grow without control.

The below SQL query will create a loop which will delete 1000 events per iteration. This can be change upon your requirements.

The @DeleteLogOlderThan defined on row 6 of the example script must match your desired date. In the attached example, the @DeleteLogOlderThan is 'Dec 4, 2011 12:00AM', which means events created before this date and time (GMT) will be deleted.

WARNING: To perform this operation, you must have direct access to the Venafi Trust Protection Platform Database and read / write permissions against it. This will permanently delete events from the database.

Script for Microsoft SQL: