Delete! Forcing Microsoft CRM 4.0 to clean up deleted records...

Aaron Elder Wrote this:
-----------------------

As you may or may not know, when a user deletes a record in Microsoft CRM via the UI or SDK, the record is not actually deleted right away.  What actually happens is that the entities DeletionStateCode field is updated from 0 to 2.  A process later comes along and cleans up the records that have been "marked for deletion" and it does so in the the proper order so that there are no referential integrity errors.  This is done because the DELETE operation in SQL is relatively expensive compared to an UPDATE.

In Microsoft CRM 1.x and 3.0, this asynchronous delete operation was handled via a windows service installed on CRM server named Microsoft CRM Deletion Service.  In Microsoft CRM 4.0, this procedure has been moved to use the new Asynchronous Operation entity and is handled the via the same system that handles Workflow triggers and all other asynchronous "jobs" within Microsoft CRM 4.0 such as Duplicate Detection process and asynchronous plug-in calls.  The time and frequency at which the deletion operation runs is controlled at the Deployment level and this can be tweaked using this tool; the default is to run every 24 hours.

Back to the story at hand...

During development, testing and data migrations, it may be useful to force Microsoft CRM to run this clean-up operation immediately.  In Microsoft CRM 3.0 this could be done by running the deletion service via the command prompt like so:

crmdeletionservice.exe -runonce

Unfortunately, now that this operation is part of the Asynchronous Service, this method no longer works.  Instead, I have developed the following SQL script and proceedure to get the same affect in Microsoft CRM 4.0.

1. Run the SQL script against your MSCRM_CONFIG database
   

-- ==========================================================
-- Ascentium National CRM Practice - http://www.ascentium.com
-- ==========================================================
-- This script will force Microsoft CRM 4.0 to run the deletion service
-- clean up job right away; cleaning out all records "marked for deletion".
-- After running this script, RESTART the Microsoft CRM Async Service
--
-- ==========================================================
-- DISCLAIMER:
-- This script is provided "AS IS" with no warranties, and confers no rights.
-- ==========================================================

USE MSCRM_CONFIG

UPDATE dbo.ScaleGroupOrganizationMaintenanceJobs SET
NextRunTime = getdate() -- Now
WHERE OperationType = 14 -- Deletion Service


2. Restart the Microsoft CRM Asynchronous Service

Enjoy,

This posting is provided "AS IS" with no warranties, and confers no rights.

posted at: 1:59 AM by Aaron Elder