In this blog we discuss the benefits of using the Microsoft Dynamics 365 Marketing Module!
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.
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.
Open a ticket with Microsoft as you aren't supposed to delete anything manually from the crm database.
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 =