locked
How to purge the DuplicateRecordBase table RRS feed

  • Question

  • Our CRM database is getting massive lately,and the speed is slow!

    Finally i find that the DuplicateRecordBase  table is too big,there are 18722175 records!

    I wish to know how to purge the DuplicateRecordBase  table?

    Thanks Very much!
    Sunday, October 11, 2009 2:24 PM

Answers

  • Hi,

    Check your duplicate detection rules.  You probably have a bad one that is causing many of these records.  There is no 'supported way' to clear this table.  However, if you open a ticket with microsoft they will help you analyze your dup detection rules and will most likely run a command similar to:

    --delete records older than 1 month
    delete from DuplicateRecordBase   where CreatedOn < DATEADD( month, -1, getdate())
    Alex Fagundes - www.PowerObjects.com
    Sunday, October 11, 2009 4:39 PM
  • This is a good way to purge the database?

    Select DuplicateRuleId, Count(*) Rows from DuplicateRecordBase GROUP BY DuplicateRuleID;


    Then go into the DuplicateRuleBase table and find out Rule Name based the on DuplicateRuleId (Query below). 

    Select Name, DuplicateRuleId from DuplicateRuleBase;

    To Delete the DuplicateRecordBase data you can go run this query:

    Update DuplicateRecordBase SET DeletionStateCode = 2 Where DuplicateRuleId = 'value of rule you want to delete';

    This doesn't delete the data right away, it marks the data to be deleted.  Once CRM runs its delete service (once a day) the information will be deleted.  If you want to run it sooner you can use this tool: http://code.msdn.microsoft.com/ScaleGroupJobEditor.

    You will also want to reindex these tables with the index rebuild service in the same tool.  As your index will be huge.  By doing this I was able to reduce my database from 24GB to 3GB in size (after a DBShrink once all the tables were clear).

    Now after you've purged most of the data (which is the cause of why you couldn't delete the Duplicate Detection Rule, you should be able to go into the CRM system to Delete the Duplicate Detection rule properly and this should purge the rest (mind you it won't be much data).

    Monday, October 12, 2009 2:11 AM

All replies

  • Hi,

    There is no supported way to purge the DuplicateRecordBase table.

    Guessing from the size of your table, it might be this big because of a duplicate rule that is checking too many fields. You can clear down the table with a SQL command but this is not supported. 

    I would strongly recommend that you get in touch with Microsoft Support and work with them to

    1- Disable the rule causing the increate in the table size.
    2- Delete the records in the table related to the rule.

    Hope this helps.

    Hassan.

    Hassan Hussain | http://hassanhussain.wordpress.com/ | http://www.mscrmvirtualusergroup.com/
    • Proposed as answer by Hassan Hussain Sunday, October 11, 2009 3:09 PM
    Sunday, October 11, 2009 3:09 PM
  • Hi,

    Check your duplicate detection rules.  You probably have a bad one that is causing many of these records.  There is no 'supported way' to clear this table.  However, if you open a ticket with microsoft they will help you analyze your dup detection rules and will most likely run a command similar to:

    --delete records older than 1 month
    delete from DuplicateRecordBase   where CreatedOn < DATEADD( month, -1, getdate())
    Alex Fagundes - www.PowerObjects.com
    Sunday, October 11, 2009 4:39 PM
  • This is a good way to purge the database?

    Select DuplicateRuleId, Count(*) Rows from DuplicateRecordBase GROUP BY DuplicateRuleID;


    Then go into the DuplicateRuleBase table and find out Rule Name based the on DuplicateRuleId (Query below). 

    Select Name, DuplicateRuleId from DuplicateRuleBase;

    To Delete the DuplicateRecordBase data you can go run this query:

    Update DuplicateRecordBase SET DeletionStateCode = 2 Where DuplicateRuleId = 'value of rule you want to delete';

    This doesn't delete the data right away, it marks the data to be deleted.  Once CRM runs its delete service (once a day) the information will be deleted.  If you want to run it sooner you can use this tool: http://code.msdn.microsoft.com/ScaleGroupJobEditor.

    You will also want to reindex these tables with the index rebuild service in the same tool.  As your index will be huge.  By doing this I was able to reduce my database from 24GB to 3GB in size (after a DBShrink once all the tables were clear).

    Now after you've purged most of the data (which is the cause of why you couldn't delete the Duplicate Detection Rule, you should be able to go into the CRM system to Delete the Duplicate Detection rule properly and this should purge the rest (mind you it won't be much data).

    Monday, October 12, 2009 2:11 AM