Dynamics Ax 2012 History cleanup


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)

,

8 responses to “Dynamics Ax 2012 History cleanup”

  1. Hi Kevin,

    I know that I’m spamming your posts with comments, but you got me all excited :o)

    Once again – have you considered making this solution available on github and share it with the community? We could start a AX Community that consisted of several repos, containing the specific solutions that people wanna share. We could reach out to all the people that already have some tools / sample code and ask if they wanna join.

    The concept is great and it is nice that you try to explain that we could introduce locks / blocking while running the cleanup. I see a lot of possibilities for my different AX projects.

    You rock!

  2. Hi Mötz,

    No problem 😀
    I’ve also considered publishing it on Github but I’ve used a free VSTS account (since codeplex is closing) so I could hook it up to a machine with R3 installed. If you want I can also give you access on this account.
    And maybe I should write a blog post with all github repo’s that I already know of 🙂

    • Hi Kevin,

      For all the repos you know, we might need to reach out to people and ask if they want to bundle it into a AX Community github page, so everything is easy to find 🙂

  3. Hi Kevin,

    Thanks for the very nice work here, is there any chance you can provide the label file and missing privileges?

    Kind regards
    Srini

    • Hi Srini,

      Thanks for the comment!
      I have uploaded the label file and attached it to this post but I did not do this for the missing privileges because there are other objects linked to this and it would not compile on your environment either. I suggest you link these to the standard or custom ones.

      Kind regards,

      Kevin

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.