Error: Deletion Service failed to clean up some tables

Some people have been experiencing the following error message in the event log:

Event Type: Error
Event Source: MSCRMDeletionService
Event Category: None
Event ID: 16387
Date: Date
Time: Time
AM User: N/A
Computer: Computer_Name
Description:
Error: Deletion Service failed to clean up some tables.

Microsoft has a solution for this message. See http://support.microsoft.com/kb/921391/EN-US/ for their solution.

Personally I don't feel comfortable with the solution they give. They are removing the enforced relationships for the tables which cause the issue. This will leave you with orphaned records in your database and probably the same issue when you upgrade to the upcoming titan release. Here are my steps to correctly fix this issue (partly copied from the MS solution) :

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, locate the system drive, locate the program files, locate Microsoft Dynamics CRM, locate the server, locate the bin, type crmdeletionservice.exe –runonce, and then press ENTER. You then 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 Microsoft SQL Query Analyzer. To do this, follow these steps:
a. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
b. Run the following query against the Organization Name_MSCRM database.
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'.

2. Run two queries agains the Organization Name_MSCRM database.
delete from [table name in message] where [column name in message] in (select campaignid from Campaign where DeletionStateCode = 2)

delete from Campaign where DeletionStateCode = 2

By doing this, you do delete the associated records as well. Keep in mind that once deleted, you cannot get the data back. Therefore always make a backup first!

16 comments:

Anonymous said...

worked for me. this is indeed a better solution than the one from MS.
thank you very much!

Anonymous said...

thanks alot for this article! it worked for me alright

Anonymous said...

Hi Ronald,

Do you perhaps know how to resolve this issue with sql server 2005? When trying to execute the statement "delete from Campaign where...", sql server 2005 returns an error saying "View or function 'Campaign' is not updatable because the modification affects multiple base tables".

Thanks in advance

Ronald Lemmen said...

Hi,

Make sure that you're not deleting from the views, instead delete from the database tables. For campaign the view would be called: Campaign. The database table is called: CampaignBase. The database extension table is called CampaignExtenstionBase.

It should work when deleting from campaignbase.

Dont forget to make a backup!

Ronald

Anonymous said...

Thanks!

Anonymous said...

Thanks, great article! Worked for me.

Anonymous said...

How to solve this deletion service issue is documented using sql server 2000. We have sql server 2005, the querie analyser program isqlw.exe isn't available in sql 2005. How can this issue be solved using sql server 2005?

Ronald Lemmen said...

Hi,

When you use SQL 2005, then the query analyzer doesnt exist anymore. This program is now part of the SQL Server Management Studio. In this studio you can create new queries and execute these.

Hope this helps,

Ronald

Anonymous said...

Ronald, thanks for the fast reply.
In SQL 2005 the query analyzer is part of the SQL Server Management Studio. That means that I have to get familiar with the SQL Server Management Studio. Thanks for the info.

Alex said...

I don't know why, but for me it works when running the simple delete query from sql (delete from accountbase where deletionstatecode = 2) but the deletion service failed.
Anyway, you cand find the sequence it's trying to execute with the profiler, and copy/paste it from there; I think it doesn't work just because it's Microsoft :)

Anonymous said...

very helpful post, your site is usually the first place i start looking into issues. however, in some instances it may be more beneficial to simply update the table that has the foreign key constraint and set that FK field to NULL instead of deleting the extension table record with the offending FK field.

Anonymous said...

FYI:
The 4.0 version of the CRM 3.0 program called CRMDeletionService.exe is now bundled with other functionality into one service call Microsoft ASynchronization Service; there is no ‘run once’ for deleting data. I will provide you a link to a program that will allow you to change the schedule of when the job will run automatically.
The deletion service is scheduled to run every 24 hours by default.
http://code.msdn.microsoft.com/ScaleGroupJobEditor/

Peter van Amerongen said...

We bumped into this issue with CRM 4.0 also and we've spoken with microsoft support. They said the following:

"I just went through the KB article (http://support.microsoft.com/default.aspx?scid=kb;EN-US;925115) and it doesn’t appear to work for CRM 4.0. You will need to go with your resolution of deleting the bulkoperationbase records where DeletionStateCode = 2. Then after doing that, the deletion service should clean up the rest."

ACTION PLAN
---------------
1. Create a backup of your CRM database, so that you can go back in case of disaster 2. Open SQL table BulkOperationBase and see if you have entries with DeletionStateCode = 2 there.

SELECT * FROM
BulkOperationBase
WHERE DeletionStateCode = 2

3. Delete this records manually

DELETE FROM BulkOperationBase
WHERE DeletionStateCode = 2

beast1163 said...

This article does not work for the following error, Any suggestions?

C:\Program Files\Microsoft CRM\Server\bin>crmdeletionservice -runonce
Cleaning up database, please wait...
Can't fast clean up the following tables: AttributeMap.
Can't clean up the following tables: AttributeMap.
Service successfully executed

Lana7220 said...

Does anyone know why after upgrade of CRM from 3.0 to 4.0 we have a new workflowbase table that takes up 80% of the database space (1.3G)? What is in it? Can we clean it up?

Suman Agarwal said...

Cleaning service in Doha
Cleaning service in Qatar
Nanny Service Qatar
Qatar cleaning Doha
Maids in Qatar
Best maid service Doha
Best maid service Qatar
Top maid service Doha
Qatar maid service
Qatar cleaning Qatar