The Deletion Service in Microsoft Dynamics CRM 4.0 doesn't remove the records marked for deletion

You might have already seen the following in the past. Even you marked several records for deletion (deletionStateCode = 2), the records are not removed.

The following will help to get a better understanding why the deletion service is not able to remove the records

1. Make sure the Microsoft CRM Asynchronous Processing Service is running.

2. Check the Application Event log on the CRM server to see if any errors are being logged.

3. Run scripts in KB968520 (https://support.microsoft.com/kb/968520)

–> If the AsyncOperationbase table is very large, and if the appropriate indexes aren't created, this can cause the deletion service to run extremely slowly and give the appearance that it isn't running at all.

4.  Run the following Script on the MSCRM_CONFIG db:
select * from ScaleGroupOrganizationMaintenanceJobs
where OrganizationId in (select Id from Organization where DatabaseName = '<Your Databasename>') and OperationType = 14

Note:   Change <Your Databasename> to the appropriate value.

When the results are returned.  Look for: 
     a.)  LastResultCode - If this equals 0, it was successful.  Anything other than 0 means it has failed.  
     b.)  LastRunTime and NextRunTime 
     c.)  State - Should be 0 if it's not currently running.  1 means it's currently running.

 

If the LastResultCode from step 1 is not zero, do the following:
1.  Run the following queries against the <Company Name>_MSCRM database –> These simply create temp tables that identify all records to be deleted. If either stored proc returns an error, the deletion service will not run

exec p_UpdateSubscriptionsForHardDeletedEntitiesWithDsc

exec p_UpdateSubscriptionsForHardDeletedEntitiesWithoutDsc

If an error was returned we will essentially create a duplicate of the stored proc that returned an error, but change it slightly so that it prints out what the errors are.

2. Right click on the Stored Proc in SQL Management Studio - Script Stored Procedure As - Create To - New Query Editor Window 

3. In the New Query Editor Window, change the name of the stored proc. For example by changing the following line from:

create procedure [dbo].[p_UpdateSubscriptionsForHardDeletedEntitiesWithDsc] as
To
create procedure [dbo].[p_UpdateSubscriptionsForHardDeletedEntitiesWithDsc2] as

4. Next, remove the comment symbols (--) in front of all "print" commands (There are 5 instances in each stored proc).

5. Execute the command in the window to create the new stored proc 

6. In new query window execute the new stored proc.  For example:

exec p_UpdateSubscriptionsForHardDeletedEntitiesWithDsc2

exec p_UpdateSubscriptionsForHardDeletedEntitiesWithoutDsc2

This should now print why the stored proc was failing.

 

Thank you
EMEA Dynamics CRM Team