Hi,
Important:
This steps on how to reduce the capacity consumption of ActivityPointerBase and ActivityPartyBase tables, which involves deleting custom data. Following deletion, these records will not be available anymore. Before proceeding with deleting any data for this table, please make sure to review your customizations, your workflows and plugins running on delete operations, and review cascade delete behaviors for this data, to make sure no data gets unintendedly deleted in the process. Please make sure to test these actions on a sandbox environment first before proceeding.
What is ActivityPointerBase?
The ActivityPointerBase table stores any activity or task that is performed by a user in Dynamics 365 CRM.
There are different types of activities that can be created whose links are stored in this table, along with the user and/or team associated to that activity, which is stored in ActivityPartyBase table. As such, when attempting to reduce the consumption of ActivityPointerBase, the consumption of ActivityPartyBase will also be reduced.
The following table lists the activity types which can be found in CRM (but not limited to):
| Appointment |
Letter |
| Approval |
Opportunity Close |
| Booking Alert |
Order Close
|
| Bulk Operation |
Phone Call |
| Campaign Activity and Campaign Response |
Quick Campaign |
| Case Resolution |
Quote Close |
| Customer Voice Survey Invite and Response |
Recurring |
| Email |
Service Appointment |
| Fax |
Social Activity |
| Incident Resolution |
Task |
| Untracked Email |
Custom activity entities |
Please bear in mind that this includes not just OOB activity entities, but also any custom activity entities that users may have created in Dynamics 365.
How to retrieve the data from ActivityPointerBase
1. Using Advanced Find and Chart Capabilities
Through Advanced Find, you will be able to select the Activities entity and retrieve all the data that is present in this table; alternatively, you can also filter by Activity Type and Status, in order to get more streamed results.
Users can also leverage the Chart built-in functionalities to display activities records by activity type, in order to understand what records are occupying the most space in the table. This is accessible by going to Activities, change the view to All Activities (for example), select “Show Chart” and filter the Activities by Type.
Example chart:
2. Using SQL Management Studio,
We can use SQL Management Studio to query our online environment data, by establishing a connection to the database as per the below (make sure the TDS endpoint setting is enabled in the environment settings in the Power Platform Admin Center):
Example query:
select activitytypecodename as 'Activity Name', ActivityTypeCode as 'ActivityCode', count(*) as 'Number of Records'
from [dbo].[activitypointer]
group by ActivityTypeCode, activitytypecodename
order by [Number of Records] desc
Example output:
Through this query, the user will be able to understand the type of activities and the amount of records each activity type currently has. Based on these results, we can create bulk deletion jobs targeted to address a specific activity type.
If you wish to know more on how to leverage SQL to query your CRM online database, please review the following article: Use SQL to query CDS data.
How to Delete the Data
There are also two options to delete the data, depending on the size of the dataset – through Advanced Find or by running a Bulk Delete Job.
If you are looking to delete smaller datasets, the records can be deleted through the same window of Advanced Find. Select the records you wish to delete and choose “Delete Activity”:
For larger datasets, the best option is to create a bulk delete job targeting the specific entity type you wish to delete, such as Appointments, Letters, Phone Calls, Tasks.
Through these steps, it will be possible to reduce the consumption of ActivityPointerBase and ActivityPartyBase.
Additional Resources:
Hope this helps!
Regards,
Venkatesh N
