Personalized Community is here!
Quickly customize your community to find the content you seek.
Have questions on moving to the cloud? Visit the Dynamics 365 Migration Community today! Microsoft’s extensive network of Dynamics AX and Dynamics CRM experts can help.
2021 Release Wave 2Discover the latest updates and new features releasing from October 2021 through March 2022.
2021 release wave 2 plan
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Community | FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Upcoming TechTalks | All 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