Database bloat, Asyncoperation (System Job) Maintenance, and Dynamics CRM Online

We’ve been running Dynamics CRM Online for several years now and our database has continued to grow to the point we were having to add storage again. We requested a list of the top 10 largest tables in our database from Microsoft support and found to our surprise that the Asyncoperation Base table was triple the size of any other table in our database. It was a surprise to us because there is a standard bulk delete job in all CRM Online organizations called “Delete completed system jobs” that sounds like it should have been maintaining the size of this table and only keeping things around that completed in the last month.

For years, we’ve known that maintenance of this table was important on premise-based installations, and have referred back to this KB Article many times to help customers perform this maintenance in a supported way directly in SQL.  We also knew we wouldn’t be given access to SQL in the Online environment, so wanted to replicate the functionality of that KB in the Online environment.  When you look closely, it’s taking action on specifically the records captured by this SQL Query:

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

If you convert that into a Bulk Record Deletion job in CRM Online, and add the clause to only delete those older than 1 month, it translates into this query:

Async Query

The system job types, when expanded, looks like this:

Job Types Expanded

You may also want to remove the Status Reason clause limiting it to only Succeeded and Cancelled jobs.  The only other ones left are Failed, and if they failed over a month ago, you may be OK with deleting the job recording that.  It’s up to you.

In our case, running this job deleted over 2 million records and freed up over 10GB of storage in our database.  The system feels snappier too.

I hope this tip works for you!

Bart

The following two tabs change content below.

Beth Ingebretson

Categories: Customer Engagement.