Dynamics Community Forum Thread Details

Hi Folks,

We are facing database capacity issues with activity pointer, we found out that email data is huge, we tried creating retention policy on activity table directly but we got this error: "Direct retention request is not supported for the table" however, we want to retain our email activities in long-term retention, and we were able to create retention policy on it. However, while we were deploying, we got this error: "email: Entity is not available for archival."

As per the error message and in microsoft documentation, it seems that it doesn't allow system tables (activity table) for archival.

Now first of all, is this true, and I've alternative approach to store description/attachment in blob, and link it with the email/activity table. However, I would like to know if there is any better way to do it.

Categories:

Dynamics 365 general

Hello GR-16010658-0,

You've hit on a significant and often misunderstood point about Dataverse long-term retention policies and system tables like ActivityPointer (which includes email activities). Let's clarify and explore your options.

Understanding the Limitations:

System Table Restrictions:
- As you've discovered, Microsoft's documentation and the error messages you're receiving confirm that direct retention policies on system tables, including ActivityPointer, are not supported.
- This is a fundamental limitation of the Dataverse long-term retention feature.
- The ActivityPointer table, being a core system table that manages various activity types (emails, appointments, tasks, etc.), is treated differently than custom or standard user-created tables.
Email Entity Archival:
- The "email: Entity is not available for archival" error also reinforces that the Email entity, which is a specific activity type within ActivityPointer, is not eligible for archival via the standard retention policies.

Why the Limitations?

System Table Dependencies: System tables are often tightly coupled with core Dataverse functionality. Archiving them directly could lead to data integrity issues or application instability.
Performance Considerations: Archiving large volumes of data from system tables could have a significant performance impact on the Dataverse platform.
Data Consistency: The ActivityPointer table is used to track related activities across various entities. Archiving it could disrupt these relationships.

Alternative Approaches:

Blob Storage for Email Content (Your Proposed Approach):
- How it Works:
  - Extract the email description/body and attachments.
  - Store them in Azure Blob Storage.
  - Replace the email description and attachment data in Dataverse with links to the Blob Storage location.
- Pros:
  - Reduces Dataverse storage consumption.
  - Provides a scalable and cost-effective solution for large email content.
- Cons:
  - Requires custom development (plugins, Power Automate flows).
  - Adds complexity to data retrieval.
  - You'll need to manage the Blob Storage lifecycle.
  - You'll need to maintain the link between dataverse and blob storage.
- Implementation Considerations:
  - Plugin/Flow Trigger: Trigger on email creation or update.
  - Blob Storage Structure: Define a logical structure for storing email content in Blob Storage.
  - Link Storage: Store the Blob Storage URL in a custom field on the ActivityPointer or Email entity.
  - Data Retrieval: Implement logic to retrieve email content from Blob Storage when needed.
Summary/Metadata Approach:
- How it Works:
  - Instead of storing the entire email content, store only essential metadata (sender, recipient, subject, date, etc.) in Dataverse.
  - Move the full email content to an external system (e.g., a dedicated email archiving solution).
- Pros:
  - Reduces Dataverse storage footprint.
  - Preserves essential email metadata for reporting and searching.
- Cons:
  - Requires integration with an external email archiving system.
  - Users will need to access the external system to view full email content.
  - You will need to maintain the link between dataverse and the external system.
Dataverse Long-Term Retention with Summary Records:
- How it Works:
  - Create a custom table to store summary records of the emails.
  - Copy essential email data to the summary records (sender, recipient, subject, date, etc.).
  - Enable long-term retention on the custom summary table.
- Pros:
  - Allows you to use Dataverse's long-term retention capabilities.
  - Preserves essential email data within Dataverse.
- Cons:
  - Requires custom development to create and populate summary records.
  - Does not archive the full email content.

Recommendations:

Blob Storage (Most Flexible): Blob Storage offers the most flexibility for managing large email content.
Prioritize Metadata: If full email content is not essential, focus on storing metadata.
Evaluate External Archiving: If you have compliance or regulatory requirements, consider a dedicated email archiving solution.
Testing: Thoroughly test any solution in a non-production environment.

Key Takeaways:

Direct retention policies on system tables are not supported.
Blob Storage provides a viable solution for managing email content.
Carefully consider your data retention requirements and choose the most appropriate approach.

If my answer was helpful, please click Like, and if it solved your problem, please mark it as verified to help other community members find more.
If you have further questions, please feel free to contact me.

My response was crafted with AI assistance and tailored to provide detailed and actionable guidance for your Microsoft Dynamics 365 query.

Regards,
Daivat Vartak