How to reduce SubscriptionTrackingDeletedObject table in CDS/Dynamics 365 – Updated!

How to reduce SubscriptionTrackingDeletedObject table in CDS/Dynamics 365 – Updated!

by | Aug 12, 2019

One of my customers is a B2C customer with a very large online database exceeding 500 GB. With a very active Marketing automation tool interated, we generate a lot of data in Dynamics 365 CE which after defined retention periods needs to be removed. This has caused some side effects, that a table called SubscriptionTrackingDeletedObject has become very large. This article will describe how to set a configuration to reduce its size. UPDATED – Based on some new learning and information from Microsoft this article has now been updated!

We often monitor the Organizational Insights, and now lately the brand new capacity feature that can be found in the left hand menu in https://admin.powerplatform.microsoft.com, if you have a CDS/Dynamics 365 CE instance.

An interesting table started growing rapidly and we had no clue what this was, and I had during my now 15 years of working with Dynamics 365 never seen it. It was called SubscriptionTrackingDeletedObject. When I came back from my Swedish summer vaccation, it had grow to over an amazing 181M records. Time to fix this.

First thing, as usual is of course to google it (yes, it is a verb, get used to it). All I found was this somewhat informative post by my good friend Chris Cognetta who is an ace with infrastructure issues.

http://cognettacloud.net/2016/06/21/crm-database-log-growth-issue/ 

However, it seemed that they just truncated the table, and we were online so that was a bit tricky, to say the least. I was at this time a bit upset that Microsoft were taking up around 50GB of space for my customer without giving me any way of managing that, or having any direct use of it. I counted to ten and called Microsoft Support.

After a few emails back and forth, the excellent support technician at Microsoft informed me that there is actually a setting in the infamous super secret setting tool with the Star Trek-sounding name OrgDBOrg (it is pronounced “Org-D-Borg” in case you ever get stuck in Dynamics trivia). The setting is called ExpireSubscriptionsInDays. I will quote the support technician in what this table is used for and if anyone has any more information, please leave a comment.

“The SubscriptionTrackingDeletedObject table is the table that logs records for number of days before deleting inactive subscriptions as well as timed out deletion services.”

I am not sure for which purpose. If it is in regards to GDPR or some restore mechanism. I would like to know though. Default value for this i 90, which means that these logs will be stored for 90 days. The minimum they can be set to is 1. As I am currently not entirely sure what these logs are used for, I would not recommend you set them to 1, but I did set my customers to 5, hoping that this is not going to come back with a vengance.

We have during the day seen a dramatic drop in the amount of records in this table, with about 30M but and it is still ongoing, but hard to measure as there is a delay in the capacity measurement the Powerplatform admin portal.

Update! The size of the table fell to 160M rows but never below this so after some further discussions with Microsoft support they did some more investigation into this subject and came back with the following recommendation:

1. Reduce the value gradually from 90 to 60 and then on
2. Never go below 15

There is however, another related setting called ExpireChangeTrackingInDays which is located just next to the ExpireSubscriptionsInDays. This is defaulted to 30. We reduced this to 15. 

Based on these recommendations we tried 60 days and this resulted in a most dramatic drop to around 20M rows. – End of update

 

So, how do you do this? First, download the OrgDbOrgSettings tool and install it in the instance where you are having issues. Check out these links below for that:

https://github.com/seanmcne/OrgDbOrgSettings/blob/master/readme.md

https://github.com/seanmcne/OrgDbOrgSettings/releases

A word of advice regarding OrgDbOrg; don’t think that you are Captain Kirk and go flying off into the Beta Quadrant and beam every single setting just because you can. It won’t make your system better, rather the opposite. Make really sure on what you are doing and don’t even trust a blog article like this, read the KB-article linked in the tool and make up your own mind. It is a powerful tool, like a jackhammer.

After you have installed the OrgDbOrgSettings tool, you can see it and open it by clicking the display name.

Then just find the “ExpireSubscriptionsInDays” – Press “Edit” and change to whatever you would like it to be. You will typically have to confirm to save it to Dynamics 365 CE/CDS

With that done you should just have to wait for the magic to be done.

As far as I have understood these two settings and this table is used to indicate how long changes and deletes are stored in this table and related tables for integrating systems to be able to read. This can, for example be Data Export Service, the old Dynamics for Outlook client etc. Hence reducing the numbers to, for instance 15 (the lowest recommended number by Microsoft) can result in some changes not being propageted to these integrated systems in the case that the integrations or just an offline client being offline for more than 15 days. And I also got the feeling that setting it to 5 was below some internal threashold and hence wasn’t really supported despite the fact that it says in OrgDBOrg that the lowest value is 1.

Beware of Legacy forms and how to fix broken case forms after upgrade

Beware of Legacy forms and how to fix broken case forms after upgrade

by | Dec 12, 2016

Been working with the upgrade of a customer from CRM 2013 to CRM 2016 on-prem and we ran into this weird error. The case form didn’t want to work after upgrade if we didn’t turn of the turbo forms. For those of you who are not aware of this, in 2015 Spring wave, there was a revision of how forms were loaded, called the turbo forms. After this version there is a setting in the first tab of System Setting where you can turn on “Legacy form rendering” if you want to go back to the old rendering method.

However, we noticed some other strange errors, for instance bool fields with Yes/No values with No as default, were shown as “False” when the form was in create state. Once the form had been saved this error went away.

Another error we saw was that if you tried to save emails, (not send them), you got an error message. The email got saved anyway, but you got a nasty message.

However, if we switched back to turbo forms, both this email error and the bool-error disapeared. Hence, it seems the legacy form controls are not nearly as well quality tested as the turbo form versions so I would strongly suggest that you do not use them unless you have no other option, and then try to move away from them as soon as possible.

So, trying to stick to my paradigm for troubleshooting; “Find where the error is first, then what the error is” – I wanted to go back to the case entity and find out why the turbo forms didn’t work.

Rickard and I, who were troubleshooting this together, enabled the dev-mode in IE (pressing F12, if you don’t know it) and opened a case. The odd thing was that the point where it broke was in one of Microsoft’s functions. I have been unable to recreate the error now so I cannot show it. And after some nitty gritty debugging in IE, we found that the script seemed to be missing “productid” and “primarycontactid” or in laymans terms, Product and Contact.

So, I added them to the case form, and magic! It worked.

But the story doesn’t end there. As I thought that this might be a good blog article and I wanted to recreate the error to have as a screendump, I removed the fields again… and mark my surprise when the form still worked… twilightzone.

Gustaf Westerlund
MVP, Founder and CTO at CRM-konsulterna AB
www.crmkonsulterna.se

CRM 2016 Preview Guide with comments

CRM 2016 Preview Guide with comments

by | Sep 20, 2015

CRM 2016 – The Preview is under way! If you havn’t checked it out already, the Release Preview Guide is here: http://t.co/iH5IoBbF9Q! It is loaded with hot new features some will be available for both on-prem and online but some only for online, I am sad to say.

http://t.co/iH5IoBbF9QSo why is this? Perhaps you have heard or seen “Cloud First, Mobile First”, Satya Nadella’s new Mantra, it is hence not only the Dynamics CRM mantra, but the entire Microsoft Mantra, with a large and very bold M. What that spells for on-prem is unclear at the moment. Microsoft are still, as we can see developing new versions of On-prem, and there are some very large customers running CRM On-prem which cannot so easily be moved to the cloud for many reasons. I’ll get back to that further down. So, I do Think we will see on-prem being kept alive for several more years but the trend that the we are seeing in the CRM 2016 Release Preview Guide and have seen previously as well, that some features will be release first or only for online, is probably going to be even stronger. This will probably be more true in regards to integrations with integrations to aquired Products such as MSE, MDM, Power BI and so on. If you are currently thinking of starting up a CRM Project, the choice of CRM on-prem or Online needs to take this into consideration.

Ok, so there are a lot of cool new features, any of these that I especially like? Yes, of course there are!

Document Generation
One of the most common problems we have with CRM that we cannot fix without huge amounts of development or Mscrm-addons excellent Product Document Core Pack is to simply generate a Word document (Quote document) from a quote wiht a couple of quote lines. It needs to be very Quick, just a few clicks, and also modifying the template needs to be simple enough for a reasonably skilled sales manger to handle. This is something that is coming in CRM 2016, maybe not to the full extent of DCP, but at least to the basic need, and I am one of many who have asked for this since CRM 3.0. I just hope it is all I wish it is!

Guided Help

The help functionality in CRM has previously been rather hidden away and not used that much by most users. This has resulted in poor user adoption and increased cost of support and training. The Guided Help is an overlay help which can help the user along as he or she uses the system which makes it very natural.

Increased support for high demand customers in CRM Online
I Work with several high demand customers and have also trained several others and have discussed the pros and cons of CRM Online. Most of these are very hesitant to move to CRM Online due to some of the following reasons:
– Backup/Restore functionality is not granular or on-demand to their requirements. Cannot be downloaded.
– Performance tuning cannot be done
– They need to make sure/prove that the data cannot be accessed outside their network
– Data warehousing with large data – for instance tables with 10M-100M records – charts break at 50k

There are several parts of the 2016 release that address these high demand Customer needs, like
– First versions of TDE, Transparent Data Encryption – which will allow encryption of CRM data. Do read the details of this, not ful ownership of key by Customer yet.
–  Data performance Dashboard – enable admins to understand what is using system Resources to better optimize system.
– New data centers, a closer data center will decrease latency, an important factor in CRM performance.
– Bulk Data Loader – Allows for bulk data import and export to CRM. Still early version, but promising.

There are still things to do in this area though and I Think that some customers may still choose on-prem over on-line.

Making Carina (7.1) features availble for on-prem
This isn’t actually in the document, but one of the greatest things of the CRM 2016 release is that all (most anyway) of the awesome stuff from the Carina or 7.1 release will be made available for CRM On-premise. The most noteworthy of these features are (please yell in the comments if you don’t agree!)

– The new navigation – The navigation in Carina is so much better than 2015 RTM or 2013. So if you are running any of those versions, this single feature is reason enough to upgrade!
– Turbo forms – Weird name by the way. What will they call it when they make these faster, super ultra forms? 🙂 Anyway, they are great. Especially if you are have many users accessing CRM with high latency (use /tools/diagnostics/diag.aspx to check from their computers). You might need to redesign your js-code in your forms, put all libs in one per form as you cannot Control the sequence it loads, but it is worth it from a user perspective.
– Theming – Colors are nice. Make your users feel at home with some custom colors and their own logo.

That’s it. Summary over. Read the document yourself and make up your own mind and leave a comment if you feel something or not. Always nice to have a discussion!

If you are a CRM User, you can drop by CRM UG Sweden Chapter meeting on Wednesday and discuss this and Other stuff as well, 3 pm CET at Microsoft in Kista outside Stockholm.

I’d also like to thank Gus Gonzales, Donna Edwards and Adam Vero for the input for this article, it is based partly on the conversation we had yesterday on the CRM UG Medic Friday Online Event. If you are interested in attending next time we have one of our sessions, it is on dec 18. If you don’t know the concept, we sit there and you ask us impossible questions about your CRM systems and we try to answer them as best we can without looking like fools. 🙂 It is usually fun!

Otherwise, I hope to see you at the CRM UG Summit in Reno in October or at Techdays in Stockholm.

Gustaf Westerlund
MVP, Founder and CTO at CRM-konsulterna AB
www.crmkonsulterna.se