Database log not working

Question Status

Verified
Maxim_msk asked a question on 8 Aug 2017 1:29 PM

Hi All,

i wish to log all actions with ledger journals and vendor payment journals.

So i run into System administration > Database log setup and added some ledger journal fields to logging:

For example, i modified General ledger description. But if on GL journal form Options > Record info > Database log i see nothing:

What's is my mistake? Does anybody succeed in setting up journals log?

Reply
Suggested Answer
Ludwig Reinhard responded on 8 Aug 2017 2:03 PM

Hi,

A general problem with the database log is that you have to identify the correct table to log. This is not always easy because the log wizard shows you labels only, which are not unique, meaning that you might simply have selected the wrong table.

Irrespective of that, I would in general not log ledger transactions because of performance issues. Probably rethink if this is really required.

Best regards,

Ludwig

Reply
Suggested Answer
Sukrut Parab responded on 8 Aug 2017 2:28 PM

can you try steps from below article and see if it works for you. If it works you must be setting it on wrong field.

www.linkedin.com/.../dynamics-ax7-database-log-sten-baumgarten

Also as Ludwig suggested look at the performance section in the following article

technet.microsoft.com/.../dd362089.aspx

Database logging and performance

Reply
Palle Agermark responded on 8 Aug 2017 2:41 PM

I believe implementation of the database log has changed in the July 2017 / Platform 8 release. Could you please confirm what Application and Platform version you are on? That will be helpful in trying to reproduce the issue. Thanks.

Reply
Maxim_msk responded on 9 Aug 2017 1:55 AM

Palle, for now i am on Update 8. The settings to log GL journal first time i made in Update 6 so nothing changed here for me.

I checked database log on Vendor's memo field and log works. But not for GL journal.

Did anyone success in setting GL journal log?

Reply
Suggested Answer
Palle Agermark responded on 9 Aug 2017 3:55 AM

The database log implementation is changed in Update 8. Writing to the database log used to be handled from the AOS. In Update 8 this is done from SQL Server triggers on the tables in the database.

So if you have a setup from an older version and haven't run any data upgrade, I would imagine that you are missing the triggers. Could you check those?

Reply
Maxim_msk responded on 9 Aug 2017 4:39 AM

Palle, how i may run data upgrade or verify what it's done?

Reply
Verified Answer
Palle Agermark responded on 9 Aug 2017 4:54 AM

Documentation on the upgrade process is here: docs.microsoft.com/.../upgrade-latest-update

If you just want the triggers to be created, you can probably get that done by just running the wizard again.

It have tried it now for LedgerJournalTable. The tricky part being to figure out which "Ledger journal table" to create the trigger for.  There are five "Ledger journal tables" to choose between.

I chose the one having a PaymentsGenerated_IT field, as this field is only present on the actual LegderJournalTable table. It's the fourth "Ledger journal table" on my system:

That creates this trigger in SQL:

 

And this is the result from a small test:

A side note is that is seems like the trigger is not removed again from the database if you remove a table from the setup. I consider that to be a bug.

Reply
Axaptus responded on 9 Aug 2017 8:45 AM

Hi Palle, thanks for sharing this info.

I have a doubt about how to monitor create/update/delete events globally.

I've built a solution which is based on the callbacks that kernel calls when using the Events framework. About kernel triggering you mentioned in your recent blog post, "you can still find the old application code in Update 8, but it is not triggered anymore."

Do you refer to the DatabeseLog events only (logInsert, logUpdate, logDelete, logRename)? Or also EventCUD triggers were included? (eventInsert, eventUpdate, ...)

Is there a better way to monitor these events? (delegates, kernel events, etc)

I hope someone can throw some light on this.

Thanks!

Reply
Suggested Answer
Palle Agermark responded on 9 Aug 2017 1:38 PM

I was thinking about the logInsert, logUpdate, logDelete and logRenameKey methods in the Application class. You can still see them, but they don't seem to be called anymore.

The EventCUD triggers on SysDataBaseLog are also not called, since the records are now being inserted directly from the database, outside the AOS of Dynamics.

I'm working on a solution where we need to track CUD dynamically on user specified tables. Earlier we have piggybacked on the database log, but now we also create triggers. Since both the database log now and earlier the "Cross-company data sharing" feature does this, I think it is a viable approach to take. You can check the code in the DataSharing model to see how triggers can be created from Dynamics. I haven't checked where the code is for creating the database log triggers, but that is probably more relevant for the stuff you work on.

To build something inside Dynamics with event handlers, Lane Swenka has made this nice solution, or proof of concept if you want: www.axdeveloperconnection.it/.../runtime-eventing-dynamics365-operations

Lanes solution adds an overhead to all transactions, having a negative impact on performance for all tables, and it calls stuff that Microsoft might change without any notice. In relation to my product, I don't have the guts to use that approach.

If your stuff is covered by entities, you might be able to benefit from the change tracking that you can setup on the entity in "Data management".

I don't know of any better ways...

Reply
Axaptus responded on 9 Aug 2017 2:49 PM

Thanks Palle!

I'm going to check the material that you've provided.

Reply
Maxim_msk responded on 10 Aug 2017 1:41 AM

Palle, have you succeed in setting log on journal lines? Looks like i tried all tables in General ledger / Ledger journal lines and a) with some tables nothing happens b) some tables after i choose fields give me error "Unable to configure Database logging. The given key was not present in the dictionary."

Reply
Verified Answer
Palle Agermark responded on 10 Aug 2017 2:23 AM

I just tried it, and that worked ok. I went for the "Journal lines" entry having a PoolRecId field, as this field is only present on the LedgerJournalTrans table.

In regards to your errors, the "Unable to configure Database logging" part comes from exception handling in the database log wizard. 

"The given key was not present in the dictionary" probably comes from some of the .Net code involved in creating the trigger.
I don't have any explanation for these errors.


I wouldn't recommend putting a log on LedgerJournalTrans though.
It is a table meant for data entry, so you'll get a huge amount of log entries, with following performance implications.

Reply
Verified Answer
Palle Agermark responded on 9 Aug 2017 4:54 AM

Documentation on the upgrade process is here: docs.microsoft.com/.../upgrade-latest-update

If you just want the triggers to be created, you can probably get that done by just running the wizard again.

It have tried it now for LedgerJournalTable. The tricky part being to figure out which "Ledger journal table" to create the trigger for.  There are five "Ledger journal tables" to choose between.

I chose the one having a PaymentsGenerated_IT field, as this field is only present on the actual LegderJournalTable table. It's the fourth "Ledger journal table" on my system:

That creates this trigger in SQL:

 

And this is the result from a small test:

A side note is that is seems like the trigger is not removed again from the database if you remove a table from the setup. I consider that to be a bug.

Reply
Verified Answer
Palle Agermark responded on 10 Aug 2017 2:23 AM

I just tried it, and that worked ok. I went for the "Journal lines" entry having a PoolRecId field, as this field is only present on the LedgerJournalTrans table.

In regards to your errors, the "Unable to configure Database logging" part comes from exception handling in the database log wizard. 

"The given key was not present in the dictionary" probably comes from some of the .Net code involved in creating the trigger.
I don't have any explanation for these errors.


I wouldn't recommend putting a log on LedgerJournalTrans though.
It is a table meant for data entry, so you'll get a huge amount of log entries, with following performance implications.

Reply
Suggested Answer
Ludwig Reinhard responded on 8 Aug 2017 2:03 PM

Hi,

A general problem with the database log is that you have to identify the correct table to log. This is not always easy because the log wizard shows you labels only, which are not unique, meaning that you might simply have selected the wrong table.

Irrespective of that, I would in general not log ledger transactions because of performance issues. Probably rethink if this is really required.

Best regards,

Ludwig

Reply
Suggested Answer
Sukrut Parab responded on 8 Aug 2017 2:28 PM

can you try steps from below article and see if it works for you. If it works you must be setting it on wrong field.

www.linkedin.com/.../dynamics-ax7-database-log-sten-baumgarten

Also as Ludwig suggested look at the performance section in the following article

technet.microsoft.com/.../dd362089.aspx

Database logging and performance

Reply
Suggested Answer
Palle Agermark responded on 9 Aug 2017 3:55 AM

The database log implementation is changed in Update 8. Writing to the database log used to be handled from the AOS. In Update 8 this is done from SQL Server triggers on the tables in the database.

So if you have a setup from an older version and haven't run any data upgrade, I would imagine that you are missing the triggers. Could you check those?

Reply
Suggested Answer
Palle Agermark responded on 9 Aug 2017 1:38 PM

I was thinking about the logInsert, logUpdate, logDelete and logRenameKey methods in the Application class. You can still see them, but they don't seem to be called anymore.

The EventCUD triggers on SysDataBaseLog are also not called, since the records are now being inserted directly from the database, outside the AOS of Dynamics.

I'm working on a solution where we need to track CUD dynamically on user specified tables. Earlier we have piggybacked on the database log, but now we also create triggers. Since both the database log now and earlier the "Cross-company data sharing" feature does this, I think it is a viable approach to take. You can check the code in the DataSharing model to see how triggers can be created from Dynamics. I haven't checked where the code is for creating the database log triggers, but that is probably more relevant for the stuff you work on.

To build something inside Dynamics with event handlers, Lane Swenka has made this nice solution, or proof of concept if you want: www.axdeveloperconnection.it/.../runtime-eventing-dynamics365-operations

Lanes solution adds an overhead to all transactions, having a negative impact on performance for all tables, and it calls stuff that Microsoft might change without any notice. In relation to my product, I don't have the guts to use that approach.

If your stuff is covered by entities, you might be able to benefit from the change tracking that you can setup on the entity in "Data management".

I don't know of any better ways...

Reply