Dynamics CRM 4 has great capabilities in helping to keep a crm system relatively duplicate free by using duplicate detection rules. However, care must be taken when creating these duplicate detection rules. Each time a duplicate detection rule runs and a match is found, the matches are written to the ‘DuplicateRecordBase’ table. If the rules are poorly written this table can drastically grow in size thus causing large database growth, async service issues, and duplicate detection issues.
A few items to keep in mind when writing duplicate detection rules.
- Watch for blanks. For example, if 90% of your contacts do not have an email address, do not create a duplicate detection rule on email address as it will be triggered 90% of the time.
- Turn off duplicate detection rules during import.
- Do not create a ton of rules. Think careful about how you can possible detect duplicates and even examine the existing data before creating the rules.
So what do you do if you do encounter a large DuplicateRecordBase table?
The first step is to identify the rule that is causing the large growth. Run this query:
as NbrRows, rules.name, rules.duplicateruleid from duplicaterecordbase as recs , duplicaterulebase as rules
where recs.duplicateruleid = rules.duplicateruleid
by rules.duplicateruleid, rules.name
This query will list the Number of Rows, followed by the duplicate detection rule name and guid.
After identifying the culprit, fix or delete the rule. If your table is not super large, deleting the culprit rule will delete the matches from the ‘DuplicateRecordBase’ table.
However, if your table is massive, this will timeout. You now have two choices: the supported method, and the unsupported method.
The supported method:
Open a ticket with Microsoft as you aren’t supposed to delete anything manually from the crm database.
The unsupported method:
First, drink a good cup of coffee and don’t do this smack in the middle of business hours.
Second – identify the ‘bad rule’ by running the query above.
Third – delete away.
where DuplicateRuleId =