Alerts… Alarming Message for Database Storage (Dynamics 365 F&O)

A recent post from Kurt Hatlevik triggered me to share my experiences with database consumption on alert-related tables. During health checks, performance reviews, or license compliance checks, I usually see data stored in tables that are either too old or should have been processed already. In this post, you will read my takeaway on alert-related findings.

Alerts

In Dynamics 365, a feature is present where users can configure alerts. This feature was already present in previous versions. As part of the system user role, all users can set up alerts and choose to get an in-app notification or email in case e.g. a record gets created or updated with specific conditions. It is also possible to set up alerts for due dates.

Before jumping into my thoughts, a quick recap on the post shared by Kurt. You can read the post by visiting his blog: D365 EventCUD and “orphan triggers” | Kurt Hatlevik – Dynamics 365 Blog

Kurt mentioned a bug that got fixed where a table EventCUD gets filled with records based on orphaned triggers. When a user creates an alert rule, a trigger gets created in the SQL database, which will be used to store a reference to the record and data values of changed records for the table where the alerting is active. These details are stored in the EventCUD table. In this case, the abbreviation CUD stands for Create, Update, and Delete. A batch job will loop through these records, check for related alert rules to determine the subscribers, and then create records in an event inbox.

In case alerts are deleted and there are no other alerts for the same table, the trigger should be deleted. This was not the case until Microsoft solved the issue, as mentioned in the post from Kurt. It would unnecessarily create records in the EventCUD table that have no relation to an active alert.

Well, let’s share some more thoughts on the alerts in Dynamics 365 Finance and Operations…

No batch job

The usage of alerts can be discussed during an implementation. Sometimes it is decided not to make use of alerts, sometimes it is not even discussed, and customers are not aware of the feature. In either case, there is no batch setup with a specific recurrence to run e.g., every minute, every 5 minutes, or at another recurrence pattern. The recurrence pattern should not exceed 8 hours. My recommendation is to have a short interval and let it run for the whole 24 hours of the day. You can read the reason below.

While the implementation team is not aware and not focused on alert rules, the end-user might find the option to create an alert rule, wondering what will happen. Being able to create alert rules is part of the System user security role, so sooner or later, the end-user will find the option and will use it. Believe me, I have seen environments in the past year where, for this reason, in some cases, a huge number of records were created in the EventCUD table, which were not processed and not notified by application management.

One example of a client that has been using Dynamics 365 since 2017 has over a million records in the table. A user created an alert rule for batch history record creation. At other clients, alerts were created even with a text like “Test what happens” for sales order lines creation. Although the performance penalty for the alerts is negligible, this has another impact. When I come across scenarios where the batch job is not running, the database consumption for this table is between 400 MB and 3 GB. As Microsoft just recently announced license enforcement for Dynamics 365 F&O, the database entitlement should also be considered. In case you have an overrun on the database consumption, each additional Gigabyte will cost about $40 a month. So consider the 3 GB as representing $120 per month, which is a storage cost of $1440 per year. I’m now only considering the production environment. This can be multiplied by the number of sandbox environments. There is no doubt that you should check your environments as well for obsolete records in the EventCUD table.

Kurt shared one option to check the contents of the EventCUD table. You can change the URL parameters to &mi=SysTableBrowser&Tablename=EventCUD. In addition, you can run a deprecated Size of company accounts report (search for table “Alerts – CUD event”) or get a table consumption list via Power Platform Admin Center.

Now we have found obsolete records, but how to clean them without delivering over a million alerts to the user? In case you enable the change-based alerts batch job, the alert rules will be evaluated, and users will receive the notifications for only the past 8 hours. Older EventCUD records are considered to be expired.

In case you want to clean all pending records, even from the possible last 8 hours, you can do this simply without scripts or involvement from Microsoft Support. As a system administrator, you can open the page System administration > Setup > Alert rules. On this page, you can find the active rules created by end-users. You can disable the rules or delete them. In case you want to support alerts after the cleaning, you might choose to deactivate the rule above by deleting it. After this, you can start the Change based alert job and ensure this will run in batch with a recurrence setting every minute for a while. The batch job processes 1000 EventCUD records per occurrence. As events older than 8 hours are deleted without alerting the user, it is recommended to have a short interval and run the batch job 24/7.

If you carefully read the post from Kurt, he mentioned asking Microsoft Support to clean the orphaned records. In case you follow my suggestion to delete the alert rule, the situation is similar to having EventCUD records created without an active rule. The processing job will take all records that have an empty Batch ID and the status Withhold. In case it can’t find an active alert rule, the record will be deleted. In the scenario from Kurt, the records were in executing status, probably caused by old logic for processing alert rules. Check the differences in the screenshot below. The first screenshot is copied from Kurt’s blog.

Notification inbox

When the events with changes are processed, data will be created in the tables EventInbox and EventInboxData. The user will get an in-app notification in the Action center.

The user can jump to the record to check more details if required. The notification can also be deleted from the action center by clicking on the X or selecting the option Clear all. The notification will be deleted for the user. Does this mean that the records from the tables are deleted? Unfortunately, the answer is No.

In previous versions of Dynamics AX, the notifications were listed in a grid on the form Notification list. This form is technically available in the application, but not linked to any menu in the application. A system administrator can open the page by altering the URL parameters to mi=EventAlertInbox. It will by default filter the alerts for the current user.

On this page, a user could mark if an alert notification was read or not. The user could also delete notifications. It makes sense to have alerts visible via the Action Center, but we also need to clean up data from these tables regularly. During some reviews on customer environments, I have seen the history of about 7-8 years, which takes up database space with old, redundant data. I will not add a possible database storage cost calculation again, but imagine similar numbers as mentioned above. For this purpose, you can find a batch job via the next navigation in the application: System administration > Periodic tasks > Notification clean-up.

Before just putting this cleanup task as a recurring batch job, you will need to think of a retention period for notifications to the users. If you don’t specify a filter, it will directly delete all notifications, even when the user has not read them or could follow up on those alerts.

On the field Alert created date and time, you can put the next dynamic query filter to set a deletion of records older than 30 days: (DayRange(-3652,-30)). Technically, this will take all dates from about 10 years ago until 30 days before today. If you want to have a shorter or longer retention period, you can change the value -30 to a higher or lower number. Higher for a longer retention and lower for a shorter period.

Managing alert rules

Each organization using Dynamics 365 F&O should monitor which alert rules are created. Potentially, a system administrator can create an alert when someone creates a new alert rule.

Prevent having alerts on tables with a lot of transactions and changes daily. This will have an additional load on your environment. About 6 months ago, I saw a scenario where over 25 salespeople got notified of sales order changes. This was on an AX2012 environment where the batch job sometimes got overloaded with the processing of alerts. As the logic for handling the EventCUD records is more efficient, we decided to backport the Dynamics 365 logic to AX2012. In a similar scenario on Dynamics 365, I would check for other tools and options, like a Power Automate Flow. Using data events, it is possible to start a flow and include conditions to manage which persons should get a notification. Keep an eye on my blog as I will share an example in the coming weeks.

There is more…

In this post, you learned the conditions when alert-related tables can grow and how to perform cleanup on these tables. It will not only keep your environment healthy, but you can also save on database storage costs. The alert functionality is just one option to save on database storage. There are many more cleanup jobs available in the application: Cleanup routines in Dynamics 365 Finance and Dynamics 365 Supply Chain Management – Finance & Operations | Dynamics 365 | Microsoft Learn.

You can also make yourself familiar with the archival features available today: Archive data in Dynamics 365 finance and operations apps with Dataverse – Finance & Operations | Dynamics 365 | Microsoft Learn

Featured image

The featured image at the top of this post is AI-generated using Microsoft Designer. The next sentence is the prompt I used for the image:

Please create an image representing an overflow of alerts in a database. It should illustrate panic or chaos.

I do hope you liked this post and will add value for you in your daily work as a professional. If you have related questions or feedback, don’t hesitate to use the Comment feature below.


That’s all for now. Till next time!