What you should know about database logging: functional impact

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:

• delete_from
• update_recordset
• insert_recordset

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:

• common.skipDataMethods(true) will skip the insert/update/delete methods
• common.skipDeleteMethod(true) will skip the delete method
• common.skipDeleteActions(true) will not execute the delete actions

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:

• Do not use it.
• If you do use it, make sure it is for a good reason and document why.
• Do not use database logging because you do not trust your employees or as a form of “security”.
• When activating the database log for a table, pay close attention to the TableGroup property of the table. It is fairly safe to activate the database log on tables with table group Main, Group and Parameter. Activating it for other groups such as Transaction, TransactionHeader, TransactionLine, WorksheetHeader and WorksheetLine is usually bad.
• If you are a consultant, capture the need for database logging in the analysis phase and set this up in your DEV/TST/ACC/… environments as early as possible.
• Do not simply activate database logging in a production environment and expect everything to go well, test it first in an other environment as if it were a code change
• Thanks for reading.