Dynamics Ax Cleaning up the AIF document log


While doing a small AIF project I wrote a small batch class to cleanup the AIF document log because the button on the AifDocumentHistory form can take up a huge amount of time. The first thing I did to write this class is checking out the standard Ax code in the following method ClassesAifMessageManagerclearAllProcessedAndError. This method uses a progress bar and deletes records in batches of 3000 records, this is something we don’t need when running in batch.

The first thing our method needs to do is check if we have access rights to delete.

if (    !hasTableAccess(tablenum(AifMessageLog),    AccessType::Delete)
    ||  !hasTableAccess(tablenum(AifDocumentLog),   AccessType::Delete)
    ||  !hasTableAccess(tablenum(AifCorrelation),   AccessType::Delete))
{
    throw error("@SYS113226");
}
Code language: PHP (php)

The second step is requesting the permission to skip AOS validation.

skipAOS = new SkipAOSValidationPermission();
skipAOS.assert();
Code language: JavaScript (javascript)

The next step is calling all the skip methods, Microsoft does this to make sure that a delete_from doesn’t fall back to row by row deletes.

  • skipAosValidation : Skips all validation methods (validateWrite, validateDelete, validateField)
  • skipDatabaseLog : Prevents SQL from making transactions logs.
  • skipDataMethods : Forces doInsert, doUpdate instead of insert, update.
  • skipDeleteActions : Skips all actions defined under DeleteActions ( For example: Deleting a SalesTable also deletes all referencing MarkupTrans records. )
  • skipDeleteMethod : Forces doDelete instead of delete.
  • skipEvents : Disables a lot of kernel events to increase performance.
aifMessageLog.skipAosValidation(true);
aifMessageLog.skipDatabaseLog(true);
aifMessageLog.skipDataMethods(true);
aifMessageLog.skipDeleteActions(true);
aifMessageLog.skipDeleteMethod(true);
aifMessageLog.skipEvents(true);

aifDocumentLog.skipDatabaseLog(true);
aifDocumentLog.skipDataMethods(true);
aifDocumentLog.skipDeleteActions(true);
aifDocumentLog.skipDeleteMethod(true);
//BP Deviation Documented
aifDocumentLog.skipAosValidation(true);
aifDocumentLog.skipEvents(true);

aifCorrelation.skipDatabaseLog(true);
aifCorrelation.skipDataMethods(true);
aifCorrelation.skipDeleteActions(true);
aifCorrelation.skipDeleteMethod(true);
//BP Deviation Documented
aifCorrelation.skipAosValidation(true);
aifCorrelation.skipEvents(true);
Code language: JavaScript (javascript)

After these methods we can start deleting the records, I’ve used a utcDateTimeRemove variable to cleanup records after a certain number of days.

delete_from     aifDocumentLog
    exists join aifMessageLog
        where   aifDocumentLog.MessageId        == aifMessageLog.MessageId
        &&      aifMessageLog.createdDateTime   <= utcDateTimeRemove
        &&     (aifMessageLog.Status            == AifMessageStatus::Processed
                                                ||
                aifMessageLog.Status            == AifMessageStatus::Error);

delete_from     aifCorrelation
    exists join aifMessageLog
        where   aifCorrelation.MessageId        == aifMessageLog.MessageId
        &&      aifMessageLog.createdDateTime   <= utcDateTimeRemove
        &&     (aifMessageLog.Status            == AifMessageStatus::Processed
                                                ||
                aifMessageLog.Status            == AifMessageStatus::Error);

delete_from aifMessageLog
    where   aifMessageLog.createdDateTime       <= utcDateTimeRemove
    &&     (aifMessageLog.Status                == AifMessageStatus::Processed
                                                ||
            aifMessageLog.Status                == AifMessageStatus::Error);
Code language: PHP (php)

The final step is to revert the code access permission.

CodeAccessPermission::revertAssert();
Code language: CSS (css)

Source : msdn xRecord class

(this job should never run on a production environment, build an archiving alternative instead)

(edit if you are using Ax 2012 have a look at this post Dynamics Ax 2012 History cleanup for a better solution)

, ,

8 responses to “Dynamics Ax Cleaning up the AIF document log”

  1. Hi Kevin,

    We would prefer to manually delete the records based on data range from within sql server. Will this cause a problem in AX?

    • Hi Jeffersong,

      Well I do this from within SQL Server as well, the script above causes locking when AIF calls occurs while cleaning up. I haven’t had any problems with the SQL Script but make sure that you run it with the absolute minimum load on AIF (if possible).

      Regards,

      Kevin

  2. Hi Kevin,

    Thanks for the great post. How can I perform this same operation across all companies using crossCompany?

    Thanks,
    Charu

    • Hi Charu,

      You could iterate your CompanyInfo table, do a changecompany and run the delete_from statements.

      Kind regards,

      Kevin

  3. Hi Kevin,

    Why do you say – this job should never run on a production environment, build an archiving alternative instead. Isn’t this just a group of logs that we won’t require after some time? Is the caution to prevent loading of production environment or to prevent loss of data?

    • Hi Sagar,

      I meant this job as is. The caution is more of a warning to write a better script then this. I’ve seen customers with high volumes on these tables and deleting a large amount at once on these tables can cause long locks and and the SQL log file can also grow too much.
      I hope this clears it up for you 🙂

      Kind regards

      • Yes that is clear now. I did clean up this table directly through SQL by deleting records in chunks of 50000 and it worked well for me. Now I will have a SQL job that would run once monthly and clean up this table leaving the latest 50000 records in it.

        Thanks for your help!

Leave a Reply

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