Choose your path Increase your proficiency with the Dynamics 365 applications that you already use and learn more about the apps that interest you. Up your game with a learning path tailored to today's Dynamics 365 masterminds and designed to prepare you for industry-recognized Microsoft certifications.
Visit Microsoft Learn
2020 release wave 1Discover the latest updates and new features to Dynamics 365 planned through September 2020
Release overview guides and videos Release Plan | Preview 2020 Release Wave 1 TimelineWatch the 2020 Release Wave 1 virtual launch event
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Upcoming TechTalks
Es I read in this forum, it happens from time to time, that the growth of the POA can exceed reasonable limits and the deletion service could get in troubles because of a timeout. As far as I remember, I had that issue in earlier days and was able to get along with that issue by cleaning up the POA as descirbed in https://support.microsoft.com/de-de/help/2664150/how-to-control-principalobjectaccess-table-growth-in-microsoft-dynamic
The thing is, the cleanup script described in that article has no errors but also deletes no more records. First i thought, ok, there's nothing more to do, but the deletion services keeps crashing every night because of a timeout. So there must be something different in what the deletion service is doing compared to the script described in the article.
I'm afraid, that the not working deletion may cause other troubles when other things are growing to big.
The SubscriptionTrackingDeletedObject table has 168k records
p_GetTablesForDeletion shows a variety of tables that have deleted records.
In another article I read that an AccessMask of '0' means no access and that the records could be deleted (if so, why doesn't the script provided by MS take this into account? But what would a privilege with no access rights granted good for?).I have 3.600.000 Records with AccessMask = 0. So I thought, if the deletion service tries to do something with those records I might understand why it doesn't complete within the standard execution timeout.
Any advices how I get the deletion service working properly again?
From what I recall a record will be picked up by the deletion Service from the POA only if AccessMask = 0 and InheritedAccessMask = 0 -> so you might need to check on that.
The SubscriptionTrackingDeletedObject stores information about deleted records, so that when an Outlook Client synchronizes with CRM - it will sync also it's own DB with the main CRM Database (it will know that the records that were deleted from CRM have to be deleted from the local DB as well). So if you have Subscribed outlook clients that didn't connect to the system for a long while - this can explain the size of that table and why it isn't shrinking. In this case, maybe it's worth checking how many Outlook Client users you have and ask them to synchronize (if there aren't that many)
The script you shared can be used one time only if you are on a higher update rollup than UR 6 for CRM 2011. If you are on higher rollups - then the system shouldn't reach the state that would be cleaned up by the script. Running it repeatedly will not delete data.
If you have timeouts - then I would suggest collecting a CRM Platform Trace together with a SQL Profiler and see the exact operation that times out. Then I would rather tackle this at a SQL Level:
- create new indexes
- increase SQL Hardware resources
- Toggle MAX DOP value
MS Support is also a good place to ask for assistance on these types of operations.
Hope this helps,
Your Reply made me think.. I wanted to avoid doing that investigations in the production Environment. Then I remembered that we have a quite accurate copy of the production database running in a test Environment. There also occured the Problem and I was able to capture the query that is causing the failure of the deletion Service.
It was as you assumed, it tried to delete POA records wer both masks are '0'. The Qeury was quite simple. I executed it manually from Management Studio which took one Minute in test and one and a half Minute in production Environment. After this I started the deletion Service Job again (set schedule some minutes in the future) and the deletion Service completed successfully.
This was the query I captured via sql server profiler. Don't know what the Version number is doing there but i assume it may be used to shift from Job run to Job run...
exec sp_executesql N'delete from PrincipalObjectAccess where (AccessRightsMask = 0 or AccessRightsMask is null) and (InheritedAccessRightsMask = 0 or InheritedAccessRightsMask is null) and VersionNumber <= @versionNumberExpired',N'@versionNumberExpired bigint',@versionNumberExpired=190509527
So I think this is fine, the job status is clean again. Thank you for your efforts!
Glad i could help
Have a great day
Business Applications communities