Search This Blog

Saturday, March 27, 2010

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.  

7 comments:

Anonymous said...

Not cool and totally unsupported. If you screw around on your customer system like this and you mess up who is going to save them? Stick to the supported methods for your sake and your customers!

Tanguy said...

It is always easy to post an aggressive comment while being anonymous...

Anyway, I'm not sure your read this post correctly... I did mention that it is unsupported and must be done carefully.

Moreover, I prefer performing unsupported operation that work instead of leaving my customer with an unusable system (Do you already try to delete, let say, 200 records when each of them take two minutes to delete?).

If I could, I would have prefered to use Microsoft support procedure but it will have take more than 80 days of SQL treatmant... In production, that's definitively not an option.

My procedure took 1 hour, it work, that's it.

And that's why I like to consider me as a CRM expert: I can propose unsupported tips to my customer to avoid him to loose time and money, what I did.

Tanguy said...

Well, I didn't want to say "it is easy to post an aggressive comment" but "it is easy to criticize"...

MDE3 said...

I have run into this issue as well. The databases that I worked on were much larger (80 gigs). I followed the instructions that are in MS's KB968520 and it took me one night to complete it.

I did not lose my running workflows or any other data that I should not have.

I don't know that I would delete the table. That process has too much risk for a CRM system.

Anonymous said...

Desperate times call for drastic measures'... Sometimes "supported methods" are unsupported by client, as result we have unhappy clients with a very poor impression of a MSCRM solution and in wich spent a very large sack money.

Is a very good unsupported solution

Regards!!!

Anonymous said...

My company's database is 119GB, with the AsyncOperationBase table being 89GB alone. I am using the script from MS on Bulk Deleting these records ... is it okay for users to be logged in and using the system while this script is running?

Thanks!

Tanguy said...

the MS scripts deletes a lot of records which consumes resources, so users can experiment slow behavior during this script