I recently had a customer ask me about the purpose of the DeletionService maintenance operation (AsyncOperationType = 14) executed by the AsyncService in Dynamics CRM 2011. To provide some context from CRM 4.0, the DeletionService performed physical data cleanup on records that had been logically deleted through the application by setting the DeletionStateCode = ‘2’. This behavior no longer occurs in CRM 2011 – when records are deleted through the application, they are physically deleted from the database. So, why the need for a DeletionService maintenance operation?
The database maintenance performed by the DeletionService operation focuses both on organization-wide cleanup and record-specific cleanup. For the latter cleanup to be performed it’s important to note that during the initial delete action while records are removed from the entity tables, they are also inserted to the dbo.SubscriptionTrackingDeletedObject table. This table provides the DeletionService with specific ObjectId’s that have been removed so that further cleanup can be performed asynchronously.
Suppose I have an existing Account:
I then delete the Account through the native application. Notice, the record is physically deleted from AccountBase, but has now been inserted to SubscriptionTrackingDeletedObject. An example of how this table is used can be seen in the stored procedure ‘p_GetTablesForDeletion’, which gets called at the beginning of the DeletionService operation to gather up entity table names where records have been deleted. It joins to the SubscriptionTrackingDeletedObject table to identify those tables.
So what type of cleanup does this DeletionService perform you say? Primarily, matchcode (duplicate detection), offline sync subscription, and principalobjectaccess/POA (inherited and shared privileges) records left behind from deleted entity records as well as a few other odds and ends.
Here are the general steps that the DeletionService performs when executing its maintenance routine:
So now you know, the rest of the story…
Microsoft Premier Field Engineer
p_GetTablesForDeletion procedure doesn't return a couple of tables such as EmailHash, ActivityParty, ListMember because this entities doesn't have IsDuplicateCheckSupported attribute set.
But SubscriptionTrackingDeletedObject can contain MILLIONS of records from this tables.
So this millions of records will remain in SubscriptionTrackingDeletedObject forever...
@Anton - The p_GetTablesForDeletion stored procedure uses the SubscriptionTrackingDeletedObject table to determine matchcode, sync subscription, and POA records that can be deleted asynchronously (separate from the physical deletion of the associated entity record). While the entity types you reference do get tracked in this deleted objects table, there is no separate cleanup to perform because these types get physically deleted with the core delete of the associated entity record. Regarding why these related types get tracked, I have no explanation.
As for the SubscriptionTrackingDeletedObject records, they should not build up unnecessarily because a separate delete statement is then executed to cleanup the tracking table after the previous operations are performed. This subsequent cleanup process removes any deleted object tracking rows with a version timestamp less than or equal to an expired version timestamp record. Hopefully this clarification alleviates your concern.
I was discussing the deletion service with my team members and your blog was the exact solution we were looking for. It was an informative read.
Thanks a lot !
We recently upgraded from 4.0. I have noticed that we have over 58 million records in the SubscriptionTrackingDeletedObject table. It is growing by 6 million records per month. If this is not expected behavior, any idea what can be done to determine the cause for this buildup of records?