by gustaf | 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.
by Gustaf Westerlund | Mar 6, 2017
Seems like that Dynamics 365/CRM doesn’t catch all dependencies for managed solutions. This is how you can find and remove one of the tricky ones I got when uninstalling a managed solution which was:
Unhandled Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=8.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: During Solution Uninstall of Solution Id [solution-guid], cannot find any instances of the component with Component Id c97cd9d7-a0fa-4ed5-b360-3c2ad998a620 and Component Type 2.Detail:
<OrganizationServiceFault xmlns:i=”http://www.w3.org/2001/XMLSchema-instance” xmlns=”http://schemas.microsoft.com/xrm/2011/Contracts”>
<ErrorCode>-2147220970</ErrorCode>
<ErrorDetails xmlns:d2p1=”http://schemas.datacontract.org/2004/07/System.Collections.Generic” />
<Message>During Solution Uninstall of Solution Id [solution-guid], cannot find any instances of the component with Component Id c97cd9d7-a0fa-4ed5-b360-3c2ad998a620 and Component Type 2.</Message>
<Timestamp>2017-03-06T13:06:54.6280227Z</Timestamp>
<ExceptionSource i:nil=”true” />
<InnerFault i:nil=”true” />
<OriginalException i:nil=”true” />
<TraceText i:nil=”true” />
</OrganizationServiceFault>
After some thinking I figured that this was probably a reference to an attribute (Component type = 2) with the Guid = c97cd9d7-a0fa-4ed5-b360-3c2ad998a620 so I opened another attribute and then cut-n-pasted the url. And it seemed that you didn’t have to worry about the entity guid:
https://<onlineorgname>.<globalcrmcenter>.dynamics.com/tools/systemcustomization/attributes/manageAttribute.aspx?appSolutionId=%7bFD140AAF-4DF4-11DD-BD17-0019B9312238%7d&attributeId=%7bc97cd9d7-a0fa-4ed5-b360-3c2ad998a620%7d&entityId=%7b70816501-edb9-4740-a16c-6a5efbc05d84%7d
Change <onlineorgname> to your org, and <globalcrmcenter> to the crm-subdomain you are using, which for EMEA is crm4.
You will then see the field editor, and can then show the dependencies of the field and remove all of them manually to make sure they are not bothering the Solution uninstall. After that “publish all” and retry removing the solution.
Gustaf Westerlund
MVP, Founder and CTO at CRM-konsulterna AB
www.crmkonsulterna.se
by Gustaf Westerlund | Nov 6, 2014
This is the third part of the series of posts in the series where I am discussing how to use the Kotter 8 steps of change method when implementing CRM in an organization. The previous posts can be found here:
Part 1 – Justification
Part 2 – Overview
“Yeah, we had planned to bring up the decision for budgeting the new CRM system at the board meeting, but we had so many urgent issues that we just didn’t have time. Maybe next time…”
Organizations everywhere are choked, especially the successful ones, the ones that have the resources to invest in CRM systems. The effect of this is that everyone can only work with the tasks at the top of their priorities. The first step of Kotter’s 8 steps of change, is to create a sense of urgency. In the case of implementing CRM, this means that we have to be very specific about the effects of what will happen if do not implement this right now.
When doing this, we have to, as always, understand the receiver, understand this party’s values and goals and try to address these. For instance, if the CEO is a very numbers oriented person you might try to work out how much time each sales person might save by using the new CRM and hence be able to put into working with selling more and generating x-% more income. If the CEO is more of a strategy and visions person, you might show her all the abilities she will get with the system and describing all the strategic advantages of this.
The bottom line is, you need to get the people with the decision power and the money to understand the importance of the CRM implementation project, in order for the project to be properly prioritized. If not, you risk being prioritized as number 11 in the list of the 10 item on the board meeting. And that dial does not go to 11.
Gustaf Westerlund
MVP, CEO and owner at CRM-konsulterna AB
www.crmkonsulterna.se
by Gustaf Westerlund | Oct 26, 2014
This is the second part in the series on how to make CRM implementations stick in an organization by using the 8 step method described by Dr. John Kotter of Harvard.
The first posting treated the fact that many organization do not consider a CRM implementation a change to the organziation of such a magnitude that it requires a change mangement approach such as the 8 step method. I think that it gives ample evidence for the reasons for why this is not so and that as many as a quarter of all failed CRM Projects could probably have been helped with better change management.
Kotter describes the following 8 step method can in broad terms be described in the following picture:
 |
| The Kotter 8 steps of Change |
The method required that you complete each step before moving to the next step. It is hence not a toolbox from which you can pick and choose the things which are easy to implement.
As the method is very general and treats many types of organizational changes I will attempt to give some examples of typical problems and suggestions from a CRM implementations perspective.
In the following parts I will discuss the steps in detail, but in short they are:
Step 1: Establishing a Sense of Urgency – many organizations have lots on their tables. If you want your CRM Project to be prioritized and have the resources required, you had better make sure people understand that they need the new CRM system RIGHT NOW. Every week without it is $$$ lost.
Step 2: Creating a Guiding Coalition – You need the backing of the right people to have a successful Project. A CRM Project without the CEO, Head of Sales, Head of Customer Service, CIO will have some issues.
Step 3: Developing a Change Vision – You need to sell the CRM implementation to the people who are going to use it. You need to create a very clear and appealing vision of where the implementation is going.
Step 4: Communicating the Vision – The vision is not going to distribute itself to the users. Create an internal marketing plan for how to get the message out to all users.
Step 5: Empowering Broad-based Action – Make sure that who want to act on the vision can and are not hindered by technology, old procedures, backward colleagues etc.
Step 6: Generating Short-term Wins – Plan for short term wins. Otherwise the risk of being stopped in your tracks Before generating anything substantial is large.
Step 7: Never letting up – Make sure to keep the pressure. There might be bugs, some people might revert to Excel or stop using the system. Monitor usage.
Step 8: Incorporating Changes into the Culture – Not until the CRM system is truly a part of the Company Culture, a part of the management meeting, board meetings, sales meetings, will you have fully succeeded.
The next part in this series will be on the first step of this, the important part of establishing a sense of urgency.
You can find Dr. John P. Kotter’s website here:
http://www.kotterinternational.com/about-us/who-we-are/john-kotter/
If you would like to read his excellent book, it is quite easy to find, for instance here: http://www.amazon.com/Leading-Change-With-Preface-Author/dp/1422186431/ref=sr_1_1?ie=UTF8&qid=1414356275&sr=8-1&keywords=Kotter
Gustaf Westerlund
MVP, CEO and owner at CRM-konsulterna AB
www.crmkonsulterna.se
by Gustaf Westerlund | Oct 25, 2014
During the CRM UG Summit I held a presentation on the subject of how to use the method described by Harvard professor John P. Kotter called the 8 Steps of Change to aid in the implementation of CRM in an organization. This is the first blog entry in a series which will discuss this subject. This first post will be on the question of if using this method is even justified?
Many companies do acknowledge that major change in the organization, like a fusion, changing from line to matrix organization, and so on, however, there are many companies that do not acknowledge that the implementation of a new CRM system is a change of such a substantial degree that it does require it to be handled as a change management Project.
If you are not already aware of the failure rates of CRM Projects, do have a look at the following links:
http://www.zdnet.com/blog/projectfailures/crm-failure-rates-2001-2009/4967
http://www.dmnews.com/63-of-crm-initiatives-fail/article/303470/
The numbers vary, if 70% failure or 30%, but the fact is that a substantial amount of Projects do fail and it is important to be humble to this in order to attempt to mitigate the possible risks of derailing the CRM investment.
Forrester researched 150 companies to find out which problems they were facing in their CRM initiatives as described here: http://blogs.forrester.com/william_band/12-03-01-dont_let_crm_pitfalls_trip_you_up
In short the problems can be divided into the following areas:
- Creating a CRM strategy – 18% strategy,
- inadequate deployment methodologies (40%)
- poorly defined business requirements (25%)
- lack of alignment on objectives (18%)
- Re-architecting customer-facing processes – 27%
- including technical/integration difficulties in supporting company processes (48%)
- poor business process design (31%)
- Selecting technology – 33%
- perceived shortcomings in vendor solutions (30%)
- a lack of skills needed to implement the solution (23%)
- system performance shortfalls (19%)
- Implementing customer-centric behaviors – 22%
- slow user adoption (49%)
- poor change management and training (36%)
- difficulties aligning culture with new ways of working (15%)
Several books could be probably be written based on this. From an implementation perspective, we can note that about 25% the companies, seem to be having no issues major problems with getting their CRM Strategy correct, they have managed to re-architect their processes and the have overcome all Technical issues, but still their issues lie with the implementation of CRM within the organization. If this is not justification enough that CRM is and requires dedicated and very deliberate change management within the organization.
As I Work as a Consultant, often contracted to implement CRM for a Customer, I would also like to note that Managing change within the organization is not something a temporary external party can do, why it is important to assign internal Resources at the implementing organization to drive the change management Project.
A final note, these numbers might be demoralizing if you are looking at starting you CRM Project or have started it, but I would suggest you look at it in another light. By seeing the problems and errors that others have faced and being open and transparent about your own issues you have a better chance of avoiding these pitfalls. I talked to one of the project leaders for one of the biggest infrastructure Projects in Stockholm which will be completed Before time and within budget. I asked her if she could give me one or two reasons why they were doing so well and she answered:
- We have very senior and technically experienced people in the Project leadership committee
- We have been very risk aware from the start, and have had weekly risk meetings
Gustaf Westerlund
MVP, CEO and owner at CRM-konsulterna AB
www.crmkonsulterna.se
Recent Comments