Keep the staging tables clean with the job history cleanup and archival functionality

The data import and export process in Dynamics 365 F&O creates a staging table for each entity in the target database by default. Staging tables let you verify, clean up, or convert data before you move it. The data management framework makes use of staging tables when importing data. Once a data import is complete, this data can be deleted.

Regularly removing old staging data from entity imports is a good practice. It keeps the size of the staging tables manageable, and it helps maintain the performance of import and export operations.

The job history cleanup and archival functionality replaces the previous versions of the cleanup functionality.

You can configure staging cleanup from Data management > Job history cleanup.

When scheduling the clean-up process, the following parameters must be specified to define the clean-up criteria.

• Number of days to retain history

• Number of hours to execute the job

Number of days to retain history – This setting is used to control the amount of execution history to be preserved. This is specified in number of days. When the clean-up job is scheduled as a recurring batch job, this setting will act like a continuously moving window thereby, always leaving the history for the specified number of days intact while deleting the rest. The default is 7 days.

•Number of hours to execute the job – Depending on the amount of history to be cleaned up, the total execution time for the clean-up job can vary from a few minutes to a few hours. Because the cleanup of the mentioned tables must be done when there is no other data management activity in the system, it becomes important to make sure that the clean-up job executes and finishes before the start of business activity. A maximum execution time can be specified by setting a max limit on the number of hours the job must run using this setting.

Recurring batch – The clean-up job can be run as a one-time, manual execution, or it can be also scheduled for recurring execution in batch. The batch can be scheduled using the Run in background settings, which is the standard batch set up. 

You can check the cleanup history using the same form:

Also, you can check the Batch Job execution:

Note

If records in the staging tables are not cleaned up completely, ensure that the cleanup job is scheduled to run in recurrence. Any clean up execution job will only clean up as many execution ID's as is possible within the provided maximum hours. To continue cleanup of any remaining staging records, the job must be scheduled to run periodically.

 Thanks for reading,

Said

Acknowledgments: I want to thank my colleague Amy Flowers (Customer Engineer) for her contributions and peer review.