Monday, September 14, 2009

Managing the AsyncOperationBase table entries

Since Update Rollup 3 a new function has been available but most likely not very widely used. That is the function of AsyncRemoveCompletedWorkflows. There is a Microsoft KB article on this that you can read here. But this function makes it so that as soon as a workflow (asynchronous job) is completed it is then marked for deletion.

This hotfix is proactive only. Therefore, when the hotfix is installed and activated, only new workflow records are deleted automatically. If the AsyncOperationBase and WorkflowLogBase tables are already large because of these workflow records, you must perform a cleanup of these tables by using the Microsoft SQL Server cleanup script that is included in the following Microsoft Knowledge Base article:
968520 ( ) Performance is slow if the AsyncOperationBase table becomes too large in Microsoft Dynamics CRM 4.0 (from KB article 968755).

I have had some clients not manage this table very well and it made their system a bit slower or just waste data space. One client had over 3 million rows in this table and their Company_MSCRM database was over 15GB. I cleaned out the table and brought the database size down to 1.4GB. That's a lot of space being wasted. If you want the logs, then it's not wasted space but I doubt that every row of that table needs to be saved, so figure out what workflow types you need and keep those and clear out the rest.

Another great blog regarding this issue can be found here. Ben goes into a lot more depth and really talks through some great ways to manage your DB, especially the AsyncOperationBase table.

Hope this is helpful to others.

David Fronk
Dynamic Methods Inc.

No comments:

Post a Comment