Empty AsyncOperationBase table when it is too big

With one of my client, I came across a particularly disturbing issue. The symptoms were:

• 2 minutes to delete a single record  
• Timeout on restarting the Microsoft CRM asynchronous processing service  
• Unable to display a view on workflows or system tasks.  
• Incredibly long time wait for any SQL statement on AsyncOperationBase table

Passing a script on the organization database to identify the sizes of tables, I realized that two tables seem particularly large: 

• AsyncOperationBase (800 000 lines)
• DuplicateRecordBase (15 000 000 lines)

The total size of the database was 12GB.

 

After passing through the Microsoft support for a procedure to purge these tables, the answer was procedures that would require whole days of SQL processing, which was clearly not an option for a production server.

 

In agreement with my client, we decided to delete and recreate these tables instead of trying to empty them.

 

WARNING! The following procedure is of course unsupported and provided as is without any warranty.

 

It has the following constraints:

• 
  
  No job systems, workflow and detection of duplicates job will be retained.
• 
  
  All running workflows will also be deleted

!!! THEREFORE PLEASE START BY PERFORMING A BACKUP OF YOUR ORGANIZATION DATABASE !!!

 

The table that we want to delete the table is AsyncOperationBase. First thing to do, identify all the dependencies for that table. To do this, start deleing the table. The following screen appears:  

 

 

IDENTIFYING THE DEPENDENCY OF THE TABLE ASYNCOPERATIONBASE

Click the dependencies in the bottom right. Here is the list of dependent components:  

 

 

Before deleting the table AsyncOperationBase, so we'll have to delete all dependent items.

 

SCRIPT GENERATION TO RECREATE DEPENDANT ELEMENTS

Before that, he'll have to create the SQL scripts that will allow us to recreate the elements that will be deleted.

Right click on the database of the CRM organization, Tasks, Generate Scripts.

 

 

In the options for generating scripts, make sure you turn on all options for tables and views as to the screenshot below.

 

 

Then select the items that will recreate. We need tables, views and user defined functions.

 

 

Follow the wizard to generate scripts for the complete script to recreate the elements that we remove. When selecting items, you have therefore chosen only those items that were listed in the dependencies of AsyncOperationBase table.

 

DELETION OF DEPENDENT ELEMENTS

You can now delete items that were listed in the dependencies table AsyncOperationBase. Delete the elements with no dependencies first to go back to the table AsyncOperationBase.

 

RECREATE DELETED ELEMENTS

Once all the deleted items, use the script generated previously to recreate the elements that have been deleted.

 

CONCLUSION

Normally, you should have a "brand new CRM" (at least for system tasks, workflow logs, bulk deletion jobs, etc..).

 

For information after this procedure:  

• The size of the database is 2GB
• The asynchronous processing service restarts in no time
• Record deletion is very fast 
• Fast display of system tasks, workflow logs, etc.

After two days of intensive use of the CRM application in terms of workflow, bulk deletion, data import, there is no problem found.

 

Some tips to avoid swell these tables:  

• Planning tasks to delete bulk to clean the table of system tasks regularly.
• Disable the detection rules that are duplicates or not involving fields ever used. By default, if you create many prospect without email address, default duplicate detection rule applies and therefore generates many rows in the table DuplicationRecordBase.