In this webinar, our experts showcase a variety of demo use cases of how different components of the...
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:
select
COUNT(1)
as NbrRows, rules.name, rules.duplicateruleid from duplicaterecordbase as recs , duplicaterulebase as rules
where recs.duplicateruleid = rules.duplicateruleid
group
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.
delete
from DuplicateRecordBase
where DuplicateRuleId =
'945E3FD2-3E93-DE11-9EEE-00215AF2E1A2'
Good luck.
I have a similar problem (only 12.5 million records in our case). I've tried the supported method and the table never sems to get cleaned up by the deletion service. Assume this is due to the timeout you mention.
I have unpublished all or the duplicate detection rules, so the MatchCodexxxx tables are gone. Just want to check it's safe to just zap the DuplicateRecordBase table (no hanging orphans created etc.)
Was planning on just doing a
truncate table DuplicateRecordBase
Thanks,
Lindsey
Hi Lindsey,
Yep - that's what we periodicaly do when we have a 'runaway' dupedetection rule. I'd advise on instead of truncate.... do a delete from DuplicateRecordBase where DuplicateRuleId =
'xxxx' so only the stuff related to that single bad duplicate detection rule is deleted.
"TRUNCATE TABLE DuplicateRecordBase" seems to be a not so bad idea. I opened a ticket with Microsoft and they adviced us to deactivate the duplicate detection rules, use the truncate table command on the table, and re-activate them. They refered to the article KB 2265722 which is only active for partners...
Hope this can help,
Ben
Hi
I did the same, but now I cannot create any jobs after recreating the rules...
Did I do something wrong?