Dynamics Community Forum Thread Details

! Important:

This article contains steps on how to reduce the capacity consumption of AsyncOperationBase table, which involve deleting custom data. Following deletion, these records will not be available anymore. Before proceeding with deleting any data in this table, please make sure to review your customizations, your workflows and plugins running on delete operations, and review cascade delete behaviors, in order to make sure that no data gets unintendedly deleted in the process. These actions should be tested first in a sandbox environment before proceeding with the final delete operation.

What is AsyncOperationBase?

The first information we should be aware is that AsyncOperation table contains the data related to system jobs & events for an Environment that are going to run in an Asynchronous way (meaning, not immediately). We have an interesting, detailed information on https://learn.microsoft.com/en-us/power-platform/admin/cleanup-asyncoperationbase-table This official documentation talks about Out of the box Bulk deletion jobs (Removing completed workflow system jobs older than 30 days).

Also, is important to understand that system jobs can have three status:

Completed: This happens when the job will no longer execute any step. There are three possible substatus (or Status Reason): Succeeded (the job did what was expected), Failed (there was a failure while processing the tasks) and Cancelled (which means that by human intervention, this won't be executed)
Running: This means that the job is being processed/executed or that an action is taking place. Again, there are three possibilities/Status Reason: In Progress(the job is currently executing), Pausing (which means that a Pause operation was triggered) or Cancelling (when an administrator has triggered the cancel option).
Pending: For when the system job hasn't met the conditions to be automatically triggered, or there was a failure evaluating the conditions. There are 2 particular Status reasons on this scenario: Waiting and Waiting for Resources.

As stated on documentation, jobs that are running or pending, shouldn't be automatically cleaned. Therefore, a manual action to cancel should be triggered.

From https://learn.microsoft.com/en-us/power-apps/developer/data-platform/reference/entities/asyncoperation there are a couple interesting fields: DataBlobId and DataBlobIdName. This is due to SystemJobs being records on the table (thus, consuming Database Capacity), but also some might have files associated required for processing (a workflow with with a long list of condition stored in an XML File for example). These files will occupy File Storage capacity, and shows in our reports:

How To reduce AsyncOperation?

As from the previous documentation:

You shouldn't delete jobs that are running/waiting.
you should run custom and periodical bulk deletion jobs. It's important however to verify the conditions used in the deletion part: Status should be Completed. But in order to prevent a massive number of records going into the deletion part, we should always add a Time condition: Older than X months, Last X weeks, etc. We should always start with a condition that not too many records fulfill (for example, Older than 3 years) and progressively move to something we feel more comfortable with: "older than 3 months" for example.

One important thing we can check is: How many jobs we have that fulfill a particular condition? For example: How many System Jobs I currently have consuming File storage? By connecting to the Database in a ReadOnly mode as documented on https://docs.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query we can query the database and look for a specific set of conditions:

This query will show all SystemJobs with their type, their name, a friendly message they might have associated as well as the number of times it exists on the table (depending on the execution attempts, status, etc). Note the condition added: Where datablobid IS NOT NULL. This means that this will show records where there's a DataBlobID, meaning, there's a file associated to the system job. As a result, this query shows all SystemJobs consuming File Capacity:

With this information, we can then go to the Environment and search for those specific jobs, check if their status is Waiting/Waiting for resources, verify if there's any error associated to them, and proceed to remediate the possible failure, or Cancel the job (so it can be picked by our custom Bulk Deletion Job)

Hope it helps!