Follow

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

Applies to:

All Venafi Encryption Director versions

Venafi Trust Protection Platform up to 16.1 

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 100000 events per iteration. This can be change upon your requirements.

The ClientTimeStamp value must match your desired date. In this example, the ClientTimeStamp is 1371772800, which means events created before Fri, 21 Jun 2013 00:00:00 GMT will be deleted. Use an Epoch converter to convert your date to the correct Timestamp

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

 

MSSQL 

-- ’90’ in the below statement represents a number of days. You can change it to any value.


WHILE (
SELECT COUNT(1) FROM [dbo].[log] WHERE ClientTimestamp < DATEDIFF(SS, '01/01/1970', SYSUTCDATETIME()) - (90 * 86400)
) > 0
BEGIN

DELETE TOP(1000) FROM [dbo].[log] WHERE ClientTimestamp < DATEDIFF(SS, '01/01/1970', SYSUTCDATETIME()) - (90 * 86400)
END

 

Oracle

BEGIN
LOOP
DELETE FROM LOG
WHERE ClientTimeStamp < 1371772800 
AND rownum < 100000;
EXIT WHEN SQL%ROWCOUNT = 0;
COMMIT;
END LOOP;
COMMIT;
END;

Also See:

Info: Set Up Log Expiration

Error: Failed to load records error message is displayed when trying to view events

Was this article helpful?
1 out of 1 found this helpful
Have more questions? Submit a request

Comments

Powered by Zendesk