Large DuplicateRecordBase Table in CRM 2011

At PowerObjects, we recently had a client that created a bad ‘Duplicate Detection’ rule in CRM 2011. This caused the ‘duplicateRecordBase’ table to grow to over 8 GB in size. “Oh no,” you may think. “The dreaded ‘duplicate record’ in CRM has reared its ugly head.” If and when this happens to you, will you be prepared? Need a process to deal with the situation?

If this ever happens to you, you will need to do the following:

1) To identify the name of the rule and the number of all the records in the CRM database; you will need to run the following script in SQL on the <organization_MSCRM> database

select distinct a.duplicateruleid, b.name, count(*) as Total from duplicaterecordbase a

join duplicaterulebase b on a.duplicateruleid=b.duplicateruleid

group by a.duplicateruleid,b.

2) This table can be cleaned up by simply truncating it.  Please note that this is the only CRM table that can be safely truncated.  We can do this because the table will immediately re-populate itself the next time a rule is executed.  Note – it is best practice to take a backup of the database before running this type of SQL statement

To truncate the duplicateRecordBase table you will need to run the following statement in SQL again the <organization_MSCRM> database.

Truncate table DuplicateRecordBase

3. Disable or modify the bad Duplicate Detection rule

Possible causes and prevention tips:

• This can be caused by a rule in which the criterion does not work well with the data in the organization.
• Having NULL values matching with each other, forces the table to grow exponentially.

Note: Update Rollup 5 for the CRM server contains an enhancement for Duplicate Detection which always the rule to ignore NULL values.

To take advantage of this install UR5 and follow the steps below:

1. Open the Duplicate Detection rule
2. Click on Administration
3. Check the Ignore Blank Values
4. Check Exclude inactive matching records

If you are having this issue in CRM 4.0 please see our blog regarding a large DuplicateRecordBase table in CRM 4.0.

Happy CRM’ing!

• Bio
• Latest Posts

JoeCRM

Microsoft Dynamics CRM Expert at PowerObjects

Joe CRM is a CRM superhero who runs on pure Microsoft Dynamics CRM adrenaline. As the face of PowerObjects, Joe CRM’s mission is to reveal innovative ways to use Dynamics CRM and bring the application to more business and organizations around the world.

Latest posts by JoeCRM (see all)

• How to Assign a Territory to a Lead in Dynamics CRM - May 17, 2013
• Out of the Box Report: Dynamics CRM User Summary - May 16, 2013
• Dynamics CRM / XRM Integration with GIS and PowerMap - May 15, 2013