What you should know about database logging: functional impact

August 11, 2014 at 16:00
filed under Dynamics AX, RealDolmen Experts
Tagged , , , , , , ,

Performance impact

As a developer, I am not a big fan of database logging, but many customers and consultant love it. Most developers will probably agree that is undermines many of the performance optimizations that developers do, like using set based operations. It is no coincidence that a whole section is devoted to performance on the Configure and manage database logging page on MSDN.
But I don’t want to talk about performance now, I want to talk about the functional impact of activating database logging.

Functional impact

I will start with the conclusion:

Activating database logging on certain tables can change how Microsoft Dynamics AX behaves and cause hard to explain bugs. It can also cause loss of data.

Okay, so why is this?

To improve performance of certain processes, Microsoft Dynamics AX sometimes uses the following set-based operations:

These operations delete/update/insert multiple records in one trip to the database instead of making one call per record. Microsoft Dynamics AX does this only when the delete/update/insert methods of the table is not overwritten. For example, if you use delete_from but you have overwritten the delete() method on the table for which you are deleting records, Microsoft Dynamics AX will revert to a record by record delete instead of a set-based operation.
To counter this, you can call a number of skip* methods:

Also remember that this will only work if you are doing set-based operations, if you use the skipDeleteAction(true) method in combination with the delete() method, the delete actions will still be executed. The skip* methods are only taken into account when you use a set-based operation such as delete_from.

When you activate the database log for a certain table, all set-based operations are converted to row-based operations, as confirmed by MSDN:

When logging is enabled for a table, all database operations that would be set-based are downgraded to row-based operations. For example, if you are logging inserts for a table, each insert is performed as a row-based insert.

When we combine all of this knowledge, we realize that activating the database log on a table will cause all code that uses the skip* methods to behave differently, that is to say, the call to these methods will be ignored.

We’ve had problems with this on our project in two cases on AX 2012 FP; namely with code that deletes WMSSHipment and VendInvoiceInfoTable records in this way. In both case, because database log was active, records were being deleted that should not have been deleted. A developer can experienced the problem as for example “skipDataMethods does not work” or “skipDeleteActions does not work“. The problem of course is not the skip* methods but the database log.

What to do about it?

If you really want to activate database logging but you have code that need to do a set-based operation, you can get around this issue by using the skipDatabaseLog method in combination with the other skip* methods.

However, in my opinion it is better not to use database log in the first place. So these are my recommendations about database logging:

6 comments

RSS / trackback

  1. Thomas Widmer

    on August 13, 2014 at 14:56

    Being more of a consultant (as well as a developer) myself, I beg to disagree.
    The Database Log is invaluable in many customer situations and for certain regulatory environments it is a mandatory tool.

    Yes, the database log has performance implications, and you have to be aware of those. But in my opinion your post gives another reason, why skipping the data methods is bad practice.

    The use of skipDataMethods(), skipDeleteMethod(), skipDeleteActions() and the corresponding doDelete(), doUpdate(), doInsert() functions endanger data integrity.
    I have experienced situations where the original use of e.g. doUpdate() was fine, but later additions to the Update() method caused errors in some obscure situations as the developer overriding the Update() method (justifiably) assumed that the method would always be called.

    So my recommendation concerning skipping of data methods would start very similar to yours:
    – Do not use them.
    – If you do use them, make sure it is for a good reason and document why.
    – If you do use them in the setting you described, warn the customer from setting up the database log on this specific table. (For all other tables, the customer can correctly assume, that the code will execute correctly with or without database log – he only needs to be mindful of the performance impact.)

  2. Klaas Deforche

    on August 14, 2014 at 16:22

    Hi Thomas, thanks for your reply and sharing your opinion.

    Of course you are right, skipping data methods and delete actions is bad practice, we don’t do that either. There are however many occasions in which standard AX does this and these are not documented.

    I used to think that activating database log only had a performance impact, and probably many think that, but that’s not the case. It can really break your system and cost you a lot of money figuring out what exactly is wrong.

    I guess the best recommendation is to think about database logging early in the project when designing the solution and making sure you develop/test with database logging activated.

  3. Krishna

    on October 31, 2014 at 11:31

    Hi,’
    “you mentioned
    We’ve had problems with this on our project in two cases on AX 2012 FP; namely with code that deletes WMSSHipment and VendInvoiceInfoTable records in this way. In both case, because database log was active, records were being deleted that should not have been deleted. A developer can experienced the problem as for example “skipDataMethods does not work” or “skipDeleteActions does not work“. The problem of course is not the skip* methods but the database log.”

    inorder to test this scenario i activated db log on my table ‘TableA’ and i run the job and have been deleted all the records …
    ‘delete_from TableA’

    could you please tell me why i got different output

  4. Klaas Deforche

    on July 29, 2015 at 11:16

    Hi Krishna,

    To test this scenario, you have to create 2 tables, TableA and TableB, that are “header and line”. With a delete action from TableA to TableB. If you delete records in TableA with skipdeleteactions, you want the records in TableB to be present after you have run the code. This is true without database logging, but with database logging, the record in tableB will still get deleted.

  5. Rav

    on December 21, 2015 at 06:36

    Delete Action – when by using cascade delete action, in method level delete_from is there in this case how to do a best practice code for future purpose.

  6. FP

    on June 17, 2016 at 14:56

    Thanks a lot !
    We’ve had the same problem with a customer on shipments and you save us a lot of wasted time

respond