Database logging for all tables

(0) Share

Report

Posted on by Community Member

The company where I work wants to log all actions (insert, update, delete, rename key) for all tables in the system. There's government regulation that requires database logging. I'm still not sure to what level, but we believe that level to be really high.

So my question is: is it possible to enable all this logging performance-wise? And what about database growth? How many times would its size multiply?

We know it would at least require great hardware to handle this scenario, and that's fine. Is there any other problem we can run into?

Thanks in advance.

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (3)

Verified answer

André Arnaud de Cal... 299,702 Super User 2025 Season 2 on at

Like (1)

Report

RE: Database logging for all tables

It is possible to log everything, but performance wise it not a best practice. Next to performance also the database growth is enormous. It also depends on the number of transactions in the system.

The problem will be the maintenance. How long do you want to keep the logging history? Who will look into it?

I think for some tables and scenario's it might be interesting to turn on database logging. But everything.... what goverment is this? I think it is ridiculous.

You can better ask if and for what entities they really need to have the logging. Normally it is to prevent or investigate fraud or possible wrong input.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Database logging for all tables

Hi, Andre

Thanks for your reply. We're a pharmaceutical industry, so there's quite serious fiscalization of our activities.

In the system we run today everything is logged. So if it's not impractical, it would be easier and faster to activate logging for everything rather than analyzing what should be logged and what shouldn't.

Good question about how long the log will be kept. I will verify that, but I'd say pretty long. Probably more than a year.

Thanks again,

Was this reply helpful? Yes No
Verified answer

Tommy Skaue Moderator on at

Like (1)

Report

RE: Database logging for all tables

Dynamics AX has a chatty database, so logging everything would be a very bad idea. I agree with André. You are better off analyzing what needs proper auditing. Dynamics AX has a pretty good system for tracking transactions between modules as data is posted between journals at various stages through the business flow. You can easily enable auditing of which user created or modified any record. Surely you can activate full audit log on all actions on any table you choose, but performance degradation will come swiftly.

Just bursting out saying "everything needs to be logged" doesn't cut it, in my opinion. You'd be shooting ducks with nuclear bombs. ;D

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Database logging for all tables

Hi, Tommy

I also agree with both of you. I tried convincing my superiors myself, but they're kinda resistant to adapt to AX. They want AX to adapt to the company, instead.

We know not everything needs to be logged, but tables that usually have the biggest impact on these matters, like items, item transactions and all of the production cycle need to.

Since there's some time before our go-live, I guess we'll try turning on the full log just to have a feel - and estimate database growth - of how the system will work with that. :)

Thanks,

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Database logging for all tables

Leandro,

It is indeed possible to do something like this, however it is not advisable. The table that captures all of this data, sysDatabaseLog, will grow quite large. Unless there is management of this table to a separate database, i.e. purging the data from the prod DB via a tool like IDMF, sysDatabaseLog will eventually dominate the DB in terms of size and record counts. This will cause issues with everything from indexing to backups and restores to creating/cloning environments. I would really have to understand more about your transactional throughput to be able to give more detailed answers, but, given the mere fact that your company wants to track ALL changes in the DB, I would not advise keeping everything in prod.

As far as database growth, I have seen sysDatabaseLog be as much as 80% of the database on its own. This was on a relatively small database, only about 100GB in an AX2009 system, but I think you can see why this would be problematic. The real question is why would anyone need this for ALL tables.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Database logging for all tables

Hi, Mike

That's an interesting suggestion. IDMF can transport the log data to another database. With that we'd lose the functionality of viewing the log directly from the actual record. We could substitute that maybe for a report. Do you know if it's possible to modify the standard log view function to access data in this other database?

Thanks a lot,

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

RE: Database logging for all tables

Leandro,

I do not know the answer to that specific question. If I were designing the system, however, I would create a report, or if appropriate cube(s) to view this data in an external data warehouse. There could be significant IO pressure put on the production system if all of these transactions were housed in production. Since this is a government regulation, it may be easier to meet reporting requirements with something external.

Thanks,

Mike

Was this reply helpful? Yes No
André Arnaud de Cal... 299,702 Super User 2025 Season 2 on at

Like (0)

Report

RE: Database logging for all tables

The information which fields has been changed and the old and new values are stored in a container field. This is not readable within a data warehouse. You have to use AX table and field numbering for extracting this data from this container field. In AX2012 these objectnumbering is installation specific.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Database logging for all tables

We too log EVERYTHING on our -- 2004 era -- AS/400 (iSeries) box -- and do not at all see any issues with logging every table given our very small box, with very little memory and without the Journaling performance option. IBM did a great job of allowing Journaling to fulfill audit and disaster recovery by allowing the data to sit in memory until the disk controllers have the time to move to the drives, and also provide functionality to guarantee it's quick delivery to a remote DR box anywhere without writing a single line of code.

We use this information constantly to determine record changes within a timeline to determine what module affected the data, using a tool that extracts the actual DB image from the journal record. Writes, before images, after images and delete images. Easy to setup, efficient, and very useful -- especially in debugging a very complex system accessed by the website, Windows servers, batch jobs, import jobs, and standard applications. These days you need tools like this to sort out how/when the target of all our effort (the DATA) is being managed by all of these complex entities.

Only those DB records that were actually changed get journaled, and as we know from the Pareto principle, only 20 percent of the records will be changed 80 percent of the time -- though based on our data I'd say that would be more like 95 to 5. Lots of activity in 20 tables, the rest just sit and get reads.

As a 34 year veteran of DB2 and the AS/400 I find it astonishing that this is an issue in a "modern" database -- all kinds of functionality lost due to the lack of investment in the specific performance issues surrounding the collection and storage of the data. When will "someone" figure out the net worth of this valuable transaction-level data and provide the support that it deserves? Provide feedback to the "someone" asking for better performance so that this information can be leveraged to our collective advantage!

We're headed to AX -- obviously leaving behind some excellent tools.

We shouldn't need_to_be afraid to implement this type of tool due to simple performance constraints -- this application and database in the modern world should be able to scale properly.

Off the soap box.

Regards to all.

Was this reply helpful? Yes No
LOKESH CHINTA 57 on at

Like (0)

Report

RE: Database logging for all tables

Here I also need to enable Database log for all the tables duration initial 1 month and I will clear the log every alternate day.

But my problem is enabling each and every log from front end is very tedious activity

can anyone guide weather there is an alternate way to enable all the log through coding . can anyone share the code

Was this reply helpful? Yes No