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
BEGINDELETE 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:
Error: Failed to load records error message is displayed when trying to view events
Comments