Is your Dynamics CRM Database Growing?

The Dynamics CRM database can sometimes grow quite large and one of the culprits could be the AsyncOperationBase table.  The good news is that Microsoft included a fix in Update Rollup 6 for Microsoft Dynamics CRM 4.0 to better manage this issue.  UR 6 will address newly added records so for those whose table size is already large, you need to take a few additional steps to clean up the existing data. 

KB 968520 contains all the information you need to clean up the existing data in your asyncoperation table.  One item to consider before applying this solution is that completed system jobs contain historical information.  You should check with Management to ensure the company is ok with not having data for completed workflows associated with the following:

• SQM data collection. – Software Quality Metrics which collects data for the customer experience program.
• Update Contract States SQL job. This job runs one time per day at midnight. This job sets the expired contracts to a state of Expired.
• Organization Full Text Catalog Index – populates full text index in db for searching kb articles in CRM.

Most companies will not mind but it is always a good practice to check before removing data.  Also, always perform a database backup before taking this type of action on a database.  It’s a good practice to have a current backup in the event that something doesn’t go as expected.

Read through the KB article carefully as there is some good information about how to optimize performance of the Deletion Service that removes records from the database that were marked for deletion, and a SQL script that you can run to show the count of the records that will marked for deletion. 

One final item for consideration is that you may want to consider running a Shrink Database procedure after the Deletion Service removes the records from the database (DBCC SHRINKDATABASE).  You can learn more about this procedure by following the link above or visit Microsoft Tech Net.  Be sure to read through all the information carefully and take SQL best practices into consideration before performing the operation.