In the brave new world of GDPR, with privacy being on everyone’s mind, it is more important than ever to understand what is happening with your data.
I was helping a client clear down a large bulk of data on their Dynamics CRM 2013 on-premise environment. I won’t go into detail, but basically all sensitive information had to be removed.
Once the deletion had completed, we noticed something strange – while all the e-mails targeted were gone, their attachments were not. My guess, as to what happened here, is that the cascade deletion logic between the E-mail activities and the ActivityMimeAttachment table is asynchronous, and due to the sheer mass of records we were clearing down, some of these operations timed out or maybe even deadlocked.
Fortunately, it seems that Microsoft have accounted for this since CRM 2011 UR16 (source), by including a cleanup job in their Deletion Service maintenance job (run on the backend server by the ‘Dynamics CRM Asynchronous Processing Service (maintenance)’ service).
However, it had been days since the deletion and yet the attachment table was still populated. Upon reviewing the Event Viewer logs on the backend server, we could see that maintenance job too was timing out. Again, just an effect of the sheer volume of data that needed to be processed – this was failing just on the querying of the table alone.
We addressed this by temporarily raising the database timeout to an hour on the backend server, by adjusting the OLEDBTimeout registry value (described in the solution section here).
We re-ran the job by re-scheduling it, and sure enough, with a bit of patience this cleaned up the orphaned records.
You can reschedule the job using this tool. I’m aware codeplex is archived, but if you download the archive for the project, and add a .zip extension to the file in CRMJobEditor.zip\releases\3\, there is the actual tool.