Updated for changes introduced in UR12 and UR16. See general steps below.
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?
I would investigate possible SQL timeouts being experienced by the maintenance service. You may need to temporarily increase this value to let the next execution "catch up". I believe the default expiration considers anything older than 90 days as targeted for cleanup. Hence, I would expect more in the neighborhood of 15-20 million records in this table based on your stated monthly volume. A second area to investigate is the age of the records in the STDO table based on the timestamp column value.
There is an attachment with Notes related to the Case. A case can have multiple documents (i.e. multiple Notes)
There is a sporadic issue that occurs when I delete a Note using code. I use the IOrganizationService.Delete method and pass the "annotation" and ID of the note.
Just before the Delete method is called I check with the Note Id in DB and it exists. After the method is called the database no longer fetches the record (since it physically deletes it) but it throws an error "Record with ID does not exists".
Any idea on why this happens?
Is there some slowness in the system that the when trying to install the record in SubscriptionTrackingDeletedObject table the note record gets deleted ?