Tips and Tricks
 
For high volume implementations database administrators may want to clear out obsolete logs to prevent excessive database sizes and improve performance. This functionality is deliberately hidden from the Manage interface for security reasons.  Where appropriate the following script can be run to clear out old records.
 
The Retention tab in Manage > Settings can be used to define how long most data in Infiniti is kept. It is better to keep these settings accurate, rather than run custom scripts on the database
 
-------------------------------------------------------

--Infiniti version 8.2 database clean up scripts

--Clears logs and inactive scheduled jobs created more than 'x' months ago

-------------------------------------------------------

--Replace the @Months variable an appropriate integer (e.g. 6 for 6 months)

DECLARE

@Months INT = 120

--Document Table temporally holds document binaries should affect 0 rows as this property is controlled in manage.

DELETE

FROM Document

WHERE DateCreated < DATEADD(MONTH, -@Months, SYSUTCDATETIME())

--Removes inactive scheduled jobs, I.e. ones that have been processed.

DELETE

FROM JobDefinition

WHERE DateCreated < DATEADD(MONTH, -@Months, SYSUTCDATETIME()) AND NextRunDate IS NULL

--Removes generation logs

DELETE

FROM Template_Log

WHERE DateTime_Start < DATEADD(MONTH, -@Months, SYSUTCDATETIME())

--Removes generation logs

DELETE

FROM ProcessJob

WHERE DateStarted < DATEADD(MONTH, -@Months, SYSUTCDATETIME())

--Removes system event logs

DELETE

FROM EventLog

WHERE [DateTime] < DATEADD(MONTH, -@Months, SYSUTCDATETIME())

Related Articles

 

Keywords

Delete unwanted data, remove old data, remove stale data dateadd