Error message when you delete a record in Microsoft Dynamics CRM: "Error: Deletion Service failed to clean up some tables"

When you delete a record in Microsoft Dynamics CRM, the following error message may be logged in the Application log:

This problem occurs because there is a constraint on the entity table of the record that is being deleted. Additionally, the user who deletes the record may not have the rights to delete all the associated records.

For example, assume that you have rights to delete only contacts. You try to delete a contact that is associated with three cases. In Microsoft Dynamics CRM, the cascade relationship between the contact entity and the case entity is set to "Cascade Delete All." Therefore, the contact record is set to a delete status in the Microsoft Dynamics CRM database. However, you do not have rights to delete cases. Therefore, the delete status is not set on the case records. When the Deletion Service tries to delete the contact record, a constraint blocks that deletion because there are three existing cases that are assigned to the contact. To successfully delete this record, you must delete the records that cause the constraint conflict.

To resolve this problem, follow these steps.

Note Before you follow the instructions in this article, make sure that you have a complete backup copy of the database that you can restore if a problem occurs.

To identify the table where the Deletion Service is failing, follow these steps:

1. On the Microsoft Dynamics CRM server, click Start, click Run, type cmd, and then click OK.
2. At the command prompt, type directorycrmdeletionservice.exe –runonce, and then press ENTER.
   
   Note By default, the Crmdeletionservice.exe file is located in the drive: \program files\Microsoft Dynamics CRM\server\bin directory.
   
   Then, you receive a message that resembles the following message:
   Can't clean up the following tables: Campaign
   Note The table that is returned in the message is the table for which the Deletion Service failed.

The following example demonstrates how to resolve this problem for the Campaign table.

To resolve the problem for the Campaign table, follow these steps:

1. Run a statement in SQL Query Analyzer. To do this, follow these steps:
   1. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
   2. Run the following query against the Organization Name_MSCRM database.
      
      Note OrganizationName is a placeholder for the actual organization name.

      delete from Campaign where DeletionStateCode = 2
      

      This query returns a message that resembles the following message:
      DELETE statement conflicted with COLUMN REFERENCE constraint 'campaign_leads'. The conflict occurred in database 'OrganizationName_MSCRM', table 'LeadBase', column 'CampaignId'. The statement has been terminated.
2. Correct the records that are causing the constraint conflict by using a statement that resembles the following statement.

   Update LeadBase set CampaignId=null WHERE CampaignId IN (SELECT CampaignId FROM CampaignBase WHERE DeletionStateCode = 2)

   Note The table and the fields that you use in the statement depend on the message that you received in step 1b earlier in this section. In this example, you are setting the CampaignId field in the LeadBase table to null if the campaign has been marked for deletion.
3. Run the following command to verify that all the tables have been corrected.

   crmdeletionservice.exe –runonce 

   If you still experience a problem, repeat steps 1 through 3 earlier in this section to correct the other constraints.