Setup Database Logging in Dynamics 365 for Finance and Operations

Database logging

The database log is a feature that helps in auditing. It keeps track of the changes made by users. You can enable track on specific actions, such as insert, delete, and update. For updates, you can turn on the tracking for specific fields. It keeps track of who created or modified the record and when. In the case of updates, you can see the previous value and the new value.

The purpose of the database log is to create audit trails for changes to critical business data. It was designed for tracking single transactions on tables that don’t change frequently. Parameter tables and master data are suitable candidates for database logging. Transactional tables on the other hand are not because the frequency of changes is generally quite high.

This blog post shows how to set up the Database Logging, but first be aware of the performance impact.

Database Log Performance Impact

Although database logging can be valuable from a business perspective, it can be expensive regarding resource use and management. Here are some of the performance implications of database logging:

• The database log table can grow quickly and can increase the size of the database. The amount of growth depends on the amount of logged data that you decide to retain.
• When logging is turned on for a transaction type, each instance of that transaction type causes multiple records to be written to the Microsoft SQL Server transaction log file. Specifically, one record is written for the initial transaction, and one record logs the transaction in the database log table. Therefore, the transaction log file will grow more quickly and might require additional maintenance.
• Database logging can adversely affect long-running automated processes, such as inventory close, calculations for bills of materials (BOMs), master planning, and long-running data imports.
• When logging is turned on for a table, all set-based database operations used to be downgraded to row-based operations. For example, if you're logging inserts for a table, each insert is done as a row-based insert. Starting from Microsoft Dynamics 365 Finance and Operations, the database logging functionality does not fall back to row-by-row operations anymore but instead uses SQL triggers. The overhead of having to write data to the SysDatabaseLog table though is still relevant. Another aspect to keep in mind using triggers is that they are using transactions and while in those transactions they will lock resources.

Setup Database Logging

Scenario: Contoso Entertainment System USA needs to log any updates to Customer credit limits.

In this procedure, you will set the system up to log any updates to the Credit limit field on a Customer record.

1. Click System administration>Setup>Database>Database log setup.
   
2. Click New to start the Logging database wizard.
   
   
   
3. Click on the Next button to view the tables.
   
4. Expand General ledger > Customers.
   
5. Select the CreditMax field and click Next.
   
6. On the Types of change form, check Update and click Next.
   
7. Click Finish to complete the setup.
   
8. D365FO provides messages to remind you of the performance impact on some tables, click Yes.
   
9. The Database Log setup for the Credit Limit field on the Customers is now added to the previously setup database logs:
   
   
   

Change Credit limit amount on a customer

In this procedure, you will update a customer credit limit to create a log record.

1. Open Accounts receivable>Customers>All Customers.
   
2. Select the customer where the credit limit value must be updated, and press Edit
   
   
3. Change the Credit limit amount and click on Save
   
   
4. If the “Recalculate credit limit” message pops up, click cancel.

View Database Log Record

In this procedure, you will view the database record that you created in the previous steps. 

1. Open System administration>Inquiries>Database>Database log.
   
   

   A new record should have been added. The Overview tab provides the Record identification, which in this case includes the customer account number and RecID of the record that was logged.

2. Click the History tab to see the values that changed for the selected record and view the contents of the changes.
   

Acknowledgments: I want to thanks my colleagues Amy Flower (Premier Field Engineer) and Kenny Saelen (Senior Program Manager) for their contributions and peer review.