kornelius.org

PLEASE NOTE my update on this on 2009-Apr-05: http://www.kornelius.org/index.php/blog/2-news/43-cleaning-the-asyncoperationbase-table-in-mscrm-4-pt-3 I previously posted about the asyncoperationbase-table in Microsoft Dynamics CRM. In the meantime I found out, that it would take several days with 100% CPU usage on our databases server to get all records deleted with the posted method. So I decided to go into deep and do it myself. I placed the following statements to the database to delete all completed jobs: update AsyncOperationBase set deletionstatecode=2 where deletionstatecode=0 and statecode=3 and AsyncOperationBase.completedon is not null delete from workflowlogbase where AsyncOperationid in (select AsyncOperationid from AsyncOperationBase where deletionstatecode=2) update DuplicateRecordBase set DeletionStateCode=2 where asyncoperationid in (select DuplicateRecordBase.asyncoperationid from DuplicateRecordBase left join asyncoperationbase on (DuplicateRecordBase.asyncoperationid=asyncoperationbase.asyncoperationid and asyncoperationbase.deletionstatecode=0) […]

I found this (http://blogs.msdn.com/crm/archive/2008/07/29/managing-size-of-asyncoperationbase-table-in-crm-4-0.aspx) very interesting article about the size of your MSCRM database. Several GB were present in ours already, mostly because of the AsyncOperationBase table. I had the change the code to get it working on my CRM 4. I remove the part about the operationtype to delete all kind of objects, but left it for this example. Here is the result: QueryExpression expression = new QueryExpression(); expression.EntityName = EntityName.asyncoperation.ToString(); ColumnSet cs=new ColumnSet(); cs.Attributes=new string[] { „asyncoperationid“ }; expression.ColumnSet = cs; ConditionExpression cState = new ConditionExpression(); cState.AttributeName = „statecode“; cState.Operator = ConditionOperator.Equal; cState.Values = new object[1]; cState.Values[0] = (int)AsyncOperationState.Completed; ConditionExpression cCompletedon = new ConditionExpression(); cCompletedon.AttributeName = „completedon“; cCompletedon.Operator = ConditionOperator.OlderThanXMonths; cCompletedon.Values = new […]