On of my most popular posts is Cleaning up the AIF document log, but this is not the only table that could benefit from a regular cleanup. For some of these standard tables there are little or no cleanup jobs, as an example the cleanup for the AIF document log only runs online in the client without any option to schedule it in batch.

I know that a lot of partners and customers use SQL scripts (as I did for the AIF post) to delete this data but there are some things to keep in mind:

• Pro:
  • Very fast.
  • No new release of models needed.
• Cons:
  • Deleting a big volume of data might cause more locks and the database log file to expand where the disk might run out of space.
  • All business logic is skipped and new customizations might be ignored. For example: a new delete action causing orphaned data.

Because of these reasons I started thinking about building a simple framework that is easy to extend, can be limited in the amount of data so database transactions and expansion of the log file is limited, and of course can be scheduled in batch.

So here’s the result:

• Type: This Enum is what makes the stuff easy to extend, the classes that do the processing use the extension framework to execute the correct logic.
• Number of days: This parameters defines the retention in a number of days.
• Number of records in transaction: the maximum number of records that will be deleted in one database transaction. If your SQL Server is configured to use lock escalation selecting a big amount of data could cause a table lock which will stop all other processes on the same table.
• Number of bulks: One transaction is one bulk. This is very useful to not over flood the database logging system. For example: if a database log transaction backup runs every hour you could schedule the cleanup to run hourly for a maximum amount of data. If the backup is finished the log file is freed up again and the cleanup can run once more.

So with this example I already provide 3 of the most used scenarios with standard Ax:

• Batch history: This job cleans the BatchJobHistory and related (delete actions) tables with the following ranges:
  • CreatedDatetime: Older then the number of days.
  • Status: Ended.
• AIF logging: This job cleans the AifMessageLog and related (delete actions) tables with the following ranges:
  • CreatedDatetime: Older then the number of days.
  • Status: Processed.
• Database logging: This job cleans the  SysDataBaseLog table with the following ranges:
  • CreatedDatetime: Older then the number of days.

If you want to extend this with other scripts for new tables all you have to do is this:

• Add your new type to the BLOGHistoryCleanupType enum.
• Make a new class that uses the BLOGHistoryCleanupAttribute with this enum value, inherit from BLOGHistoryCleanupProcessorBase and implement the run method.

The source is below, enjoy!

---

Changelog

(This tool has only been tested on a Dynamics Ax 2012 R3 CU12 environment, please test this before putting in to a production environment and use at your own risk)