Summary

This article describes how to purge history data from the adTempus database in scenarios where database performance is degraded by excessive history.

More Information

History information for each job run by adTempus is stored in tables in the adTempus database. adTempus automatically purges this data once it reaches a certain age, based on the global and job-specific history retention settings.

If these settings are not configured appropriately it is possible to end up with an excessive amount of history data, which can lead to adTempus performance issues as described in article K00000475 or in exceeding the size limit for a SQL Server Express database, as discussed in articles K00000477 and K00000147.

When this happens, it may not be possible or feasible to wait for adTempus to clear the history data as part of the normal purge cycle. In such scenarios, unwanted history data can be cleared by deleting data from the adTempus database.

See article K00000476 for information on determining history counts for jobs.

See article K00000385 for information on how to execute updates against the adTempus database.

Warning: This procedure clears history based solely on a cutoff date. All history from before the cutoff date is discarded. Improper execution of the database scripts can result in the loss of all history. Always make a backup of the adTempus database using the adTempus Database Utility before deleting data.

Begin by selecting a cutoff date. In this example we will use January 1, 2015, which is represented as "2015-01-01". In the examples, replace "2015-01-01" with the date you have selected.

To delete history for all jobs, execute the following commands against the database:

delete from executionHistoryStep where owner in (select oid from executionHistoryItem where executionStart < '2015-01-01');

delete from executionHistoryItem where executionStart < '2015-01-01';

delete from logevent where messagetimestamp < '2015-01-01';

To delete history for a particular job, identify the OID of the job you want to delete history for (the query described in article K00000476 returns the job OID along with the name and information about the history counts). Then execute the following commands against the database, replacing "<OID>" with the OID value:

delete from executionHistoryStep where owner in (select oid from executionHistoryItem where executionStart < '2015-01-01') and jobOID='<OID>';

delete from executionHistoryItem where executionStart < '2015-01-01' and jobOID='<OID>';

delete from logevent where messagetimestamp < '2015-01-01' and jobOID='<OID>';