Microsoft Dynamics 365 | Integration, Dataverse...

Checking Storage of an Entity - ActivityPointer

(0) Share

Report

Posted on by Taybo510

150

We have received alerts that we are over our Workspace storage limit (we have around 15GBs allotted but are using over 30GBs) and we are not sure of the best way to proceed. We have seen the Microsoft documentation on the best steps to clear storage space, but our number one issue is by far the size of our ActivityPointer table (it is over 15GBs by itself). Is there any way to get a detailed breakdown of what is taking the most space? I would be fine seeing it broken down by record owner, date of record creation, or really anything else besides the general size total we see on the Capacity section of the Power Admin platform. If we just blindly delete Activities that are X number of days / weeks / months old, there is no guarantee that those records were the ones taking most of the storage space. I just want to make sure we delete only what we must and in the most effecient way possible, but I am at a loss on where to bring. Does anyone know of a way to ping any entity within Dynamics to see a detailed breakdown of where the storage issues are within the entity? I have built several Dashboards using BI and can ping the Dynamics data through there but it doesn't seem to give me individual totals related to total storage space from what I can see. Open to any and all suggestions, thanks for your help!

All responses (11)

Answers (0)

Suggested answer

AriaConsulting on at

Like (0)

Report

RE: Checking Storage of an Entity - ActivityPointer

TomPhillips,

I've just posted a question on the MS Partner Yammer group to see if MS will provide an 'Official' response to this.
TomPhillips 55 on at

Like (0)

Report

RE: Checking Storage of an Entity - ActivityPointer

Yes you're right in the Online environment the attachments count under the File storage, not the Database storage. The ActivityPointerBase table seems to consume a lot of storage for a small amount of records.

We find ourselves in the same position looking to archive emails to Azure DB or delete. We dont really want to completely delete these but consume so much space. To give some context we have around email 90k records taking up 7.5GB of space. MS has said this is normal for this table in a support case we raised, this table is increasing by 1GB a month, so if we we're to add additional storage @ $40 for 1 GB of per month this would add up over the long term. From your side, how many records make up the 15GB?

As ours is only 7 months worth of emails we will need to bite the bullet and purchase additional storage to give us enough capacity to hold at least 12months of emails at the current rate of 1GB a month x 12. As we grow the use of the system though we will need to carefully consider who can track emails.
AriaConsulting on at

Like (0)

Report

RE: Checking Storage of an Entity - ActivityPointer

Chris,

The SQL Select statement only works for on-prem. And I am pretty sure you said your environment is online. For online, you have to use the method Wahaj described using FetchXML. And yes the attachments from emails are stored outside the DB. Although I'm not sure it is in ODfB. If it is, it certainly isn't a folder that's visible for me in any of my environments. I always thought it was somewhere in an Azure blob or some such. Regardless, they don't count against your DB limit anyway. They have their own limit of 20GB with the 2019-onward model.

I'm not familiar with SkyVia, so can't comment there. Maybe Wahaj is.
Taybo510 150 on at

Like (0)

Report

RE: Checking Storage of an Entity - ActivityPointer

Thank you again Wahaj and Lorne! I have been working with Microsoft, and bottom line, it does not sound like there is an easy way to see RECORD size, just ENTITY size in the Database. I was advised to clear Attachments from emails, but my understanding is that those are stored on OneDrive anyway and don't take DATABASE storage, they use FILE storage (if I am wrong, please let me know). I do have a question related to the query Wahaj recommended (SELECT TOP 1000 ActivityID FROM Email ORDER BY LEN(DESCRIPTION) DESC). I am able to ping the database with Power Query / Power BI, BUT the Description fields are all capped at 32,766 when I try to check length. Would pinging the data your way let me see the REAL length of this field per record? If not, I have a solution I have done once before that I am not a huge fan of but I know would work. I can open older emails using a third party program (SkyVia) to change their status from Completed to Draft, then clear out the Description field for these records (I leave a generic message of "Message Content Removed"). Finally, I go back and mark them all Complete again, and we at least still have the Record attached to the original Contact / Account, along with the Subject line. I did this about 2 years ago, and was able to get back roughly 10GBs of storage.

I won't use my "Description clear" method until sometime next week, and I am still open to suggestions if anyone has anything else. This isn't an ideal method, but I know it will get the Database down, and I don't have any other options as far as I can see. Thank you for your assistance! :-)
Suggested answer

Wahaj Rashid 11,319 on at

Like (2)

Report

RE: Checking Storage of an Entity - ActivityPointer

By the way, even in an on-premise environment the space has to be claimed by running ShrinkDB.

I assume Microsoft might have a routine DB maintenance that will run the same to claim the space (not sure what is the schedule though).

I suggest, first delete handful of data and then talk to Microsoft, may be they can run this routine on your request.
AriaConsulting on at

Like (2)

Report
RE: Checking Storage of an Entity - ActivityPointer

Thanks for the clarification. And Wahaj is right on as far as the tables to look at in online. He's also wayyyyy kinder than I would be in terms of the legacy "marketing" functionality!

Next, you said "but we have tried deleting old Activities before and got almost zero storage space back from it". And you're absolutely right. Here's the thing (I chatted with an MS product group PgM about this): MS eventually (key word there) gets around to reclaiming the virtual disk space, but it is not immediate. Or, at least, not necessarily. Sometimes, it may not even nbe within an x-days window. And, to be blunt, MS isn't really incentivized to offer an easier/better way to address this. Not when they can sell you more DB space......

Finally, in terms of how long to hold data, there isn't necessarily a "best practice" as that lands squarely in the realm of Records Management (an area I have quite a bit of experience with along with enterprise content management). And NO, records management is NOT just for files/documents. Data in structured systems is also very much in scope. So, how long to maintain certain types of information are driven by 2 considerations:

"Arcs" - Administrative Records - this is the data you need to keep for compliance purposes. In other words, it is driven by external bodies such as NIST, NERC, IRS, FAA and so on and so on depending on industry and location and what/how you do your business. This is the data you can get in a very, very bad place for not keeping long enough OR not disposing of as soon as you're allowed to.

"Orcs" - Operational Records - this is data/information you keep for as long as your org deems it valuable to do so. That might be 1 day or less for unimportant transient data or 3 years because your VP Sales (as an example) demands to see sales history across that time frame (and is prepared to pay the costs associated)

Here's the nice part though when it comes to email content in the activity history: I'm going to assume you're using Exchange (online or hybrid) as you would be in a world of pain trying to use anything else with D365. So, that content that gets COPIED into the activity history also lives in the source.......Hence, if you use some of M365's capabilities around managing and archiving for Exchange, you don't have any issue with the "Arcs" compliance if you scrub it out of D365. Feel free to check that with your company's lawyer. In point of fact, it's preferable, legally speaking, to manage it in the source. Thus, you really only need to decide what your "Orcs" requirements for keeping the data are.
Suggested answer

Wahaj Rashid 11,319 on at

Like (1)

Report
RE: Checking Storage of an Entity - ActivityPointer

Hi,

For emails, I prioritize clean-up based on the following:

Attachment Size: this can be found in the Activity Mime Attachment and Attachment tables.

Length for the Email Description field (where the body is stored).

As I mentioned earlier, fortunately for an on-premise environment, I can do queries like:

SELECT TOP 1000 ActivityID FROM Email
ORDER BY LEN(DESCRIPTION) DESC

For online, get the data out of this table and mark the Emails

having bigger attachments

sort by Length of Description

Again, we cannot get the actual disk size, but for sure, emails with longer bodies will take up more space.

Another, challenge is to mark such emails (add a flag and update emails candidate for delete), so you can schedule a bulk delete job in Dynamics 365.

For your second question, identify why you have so many emails? Are these actual customer collaborations or Marketing emails.

In my scenario, space-occupying emails from Marketing (and the number is huge).

So we are planning to move away from the built-in marketing module and use either Dynamics 365 for Marketing or a 3rd-party tool.

In my opinion, the built-in marketing module is not robust enough to be used in today's competitive world.

We should move to a solution meant for bulk emails.

In short, having too much data in the Activity table is always a pain.

When I design a solution, I always think of the Activity table and try to keep it in control (maybe you can call it tool limitation).
Taybo510 150 on at

Like (0)

Report

RE: Checking Storage of an Entity - ActivityPointer

Thank you for the replies Wahaj and Lorne! I should have given more detail in my question, as I do currently know how to slice and dice the ActivityPointer table to see what is in there. I know Emails are my main issue (quoted text in emails mean ONE email record could be massive), but I can't tell WHICH emails are hurting us the most. I could go back and delete all emails over X months / years old, but if those emails were relatively small, it might not do much. If there are a group of recent emails with massive amounted of text in the body, getting rid of those should help our storage faster. Is there a way to tell specifically which records are taking the most space, or is it just a guess and check game? If I can slice the data by Activity Owner (which I can right now), is there a way to see the STORAGE space used, not just total record count? Not sure that makes sense, but we have tried deleting old Activities before and got almost zero storage space back from it. I just want to make sure we are deleting things that will benefit us the most, but maybe that isn't.

Lastly, for best practices, is there a common time limit for how long people hold emails? My boss is under the impression that much larger companies than us store WAY more email data than we are, but I am not so sure. If someone wanted to store an "infinite" amount of emails, or at least several years worth, would it just mean paying extra for storage on the cloud or needing to switch to on-prem? Do Emails comparatively take massive amounts of data to store long term with Dynamics? Thank you again for your replies!
AriaConsulting on at

Like (2)

Report

RE: Checking Storage of an Entity - ActivityPointer

Well, Wahaj Rashid is defintely right about the activity tables being a pain in the ___. So, you could get into using XRM toolbox and fetch XML and all that. OR, you could be lazy like me and download the Power BI desktop client, grab your API end point, connect, and pull in all the activity tables and be able to slice and dice as desired (including using Power Query if you want/need to do any transforms (without needing to get into any coding that Wahaj is WAY better than me at) and that would enable you to be pretty confident you have identified records that can be deleted without losing data that you may need from either recordkeeping or business retention perspectives. Once you've identified the reords to delete, you can then connect using the "Get Data" panel in Excel under Data>Get & Transform Data. That provides a live connection to your instance (meaning you probably want to both do a backup and copy to your SBX and test in SBX first!). You could skip the Power BI part, but then you're working directly against live tables in Excel trying to do analysis and that kinda creates some opportunities for bad things to happen.

Here's some screenshots:
Suggested answer

Wahaj Rashid 11,319 on at

Like (1)

Report
RE: Checking Storage of an Entity - ActivityPointer

Hi,

Thank you for your query.

I am cleaning up the ActivityPointerBase these days, however, I am working on an on-premise environment. This gives me the advantage to get more statistics from the DB (like size, unused space, and querying and exporting data directly from SQL Server which is faster).

For online, its a bit difficult, but here are my suggestions based on recent experience:

Analyze the data by Activity Type (get break-down):

This step is easier on DB Level, however, when the recordset is high, it is difficult to analyze online. As far as I know, there is no direct tool available that will help you, however, you can try the following steps:

What you can do is, use XRMToolBox's Fetch XML Tester to run some ad-hoc aggregated FetchXML queries, for example, get Count by ActivityType, get County by Owner, etc. This way, you will have some idea, what kind of activity is taking more space (please note, the number of records does not translate to actual disk size, however, this still helps to have some idea).

For any reason, if FetchXML queries, do not work (takes too much time to respond or recordset is too high), either narrow down your query (lets say for 3 months) or export this data to either disk (CSV/Excel) or another database. You can export data by batches (say monthly), or write a utility (SSIS/.Net) to do this programmatically.

In most of the scenarios, Emails are one of the major contributors for ActivityPointerBase table size. The reason being Email Bodies (including Inline images) are stored in this table. I would recommend starting cleaning Emails first. Create Bulk Deletion jobs, to do this work for you, you can also start deleting emails with attachments, this will also have a good impact on size reduction.

Also, focus on Plugin Trace and Async Operation Job tables, these will be easier to delete using Bulk Deletion jobs.

Now, for your reference here is a sample FetchXML query to get Activity Table records by Type:

Here is a reference article that also says it is quite hard to get the exact size of a DB Table:

Entity Table Size in dynamics CRM online - Microsoft Dynamics CRM Forum Community Forum

Finally, there is no set of rules for this activity, you need to try whatever options you have, in other words, there is no magical tool that will help you here, feel free to reach me if you are stuck somewhere.