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 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 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!