Dynamics CRM Online Maintenance Tip – Increase Performance and Reclaim Storage

Most larger organizations who run Dynamics CRM on their own servers are very familiar with the benefits of routinely maintaining their Async Operations Base table (read system jobs).  It not only helps keep performance of the database tuned for speed, but also helps free up precious high-speed database disk space.  This post shows CRM administrators how to realize those same benefits in your own CRM Online instance.

Microsoft has had a knowledgebase article for years that addresses this, but since it’s a SQL script, you don’t have access to run it in the CRM Online environment. The key SQL statement in that article that tells you how to safely query for the right System Jobs to delete, though, is this one:

Select top (@DeleteRowCount) AsyncOperationId
from AsyncOperationBase
where OperationType in (1, 9, 12, 25, 27, 10) AND StateCode = 3

To mimic that functionality in CRM Online, and only remove jobs that were completed at least a month ago, you simply create a Bulk Record Deletion job (found under the Data Management area of Settings) that looks like this:

Bulk Delete Query

Unfortunately, you can’t see all of the System Job Types in that view. Here’s what they are:

System Job Types

I’d recommend setting up a recurrence of at least once a quarter.  Run it monthly if you have lots of workflows in your environment.

I hope this helps you keep your CRM Online instance running smoothly (and saves you some online storage cost too!).