PowerObjects Blog 

for Microsoft Business Applications


CrM 4 - Large DuplicateRecordBase Table

Post Author: Joe D365 |

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.

  1. 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.
  2. Turn off duplicate detection rules during import.
  3. 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:

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.

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.

delete
from DuplicateRecordBase
where DuplicateRuleId =
'945E3FD2-3E93-DE11-9EEE-00215AF2E1A2'

Good luck.

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

4 comments on “CrM 4 - Large DuplicateRecordBase Table”

  1. 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

    1. 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.

  2. "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

    1. Hi

      I did the same, but now I cannot create any jobs after recreating the rules...

      Did I do something wrong?

PowerObjects Recommends