Microsoft Dynamics CRM – Steve Suh

I worked on a CRM 4.0 system with rollup update #4 that took feeds from SAP and created records in CRM.  Things ran fine until adding a custom audit plug-in that would log modifications to CRM asynchronously.  After installing the plug-ins, the CRM SQL Server would go to 100% usage and start timing out for end users.

After investigating with SQL Profiler, it looked like CRM was trying to write to the AsyncOperationBase, when I took a look at the size of the table, it was over 1 million records for a relatively small CRM system. 

According to this article CRM logs all asynchronous operations to this table, however there is a bug with CRM where it won’t clear the completed operations.  After following the steps of manually clearing the completed records things worked fine.  However over time the table kept growing and would not clear out the completed operations (even though I added the registry node AsyncRemoveCompletedWorkflows). 

I ended up creating a nightly SQL Job that clears out the AsyncOperationBase, WorkflowLogBase, and BulkDeleteFailureBase tables, and broke it into 3 steps.

STEP 1: Set recovery mode to simple to prevent logging deletes, this will save you time if the table is large.  Then rebuild the index.

ALTER DATABASE [DATABASE_NAME]

SET RECOVERY SIMPLE
GO
 ALTER INDEX ALL ON AsyncOperationBase REBUILD WITH  
(FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)  

 – Rebuild Indexes & Update Statistics on WorkflowLogBase Table  
ALTER INDEX ALL ON WorkflowLogBase REBUILD WITH  
(FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)

 

STEP 2: Actual delete operations

declare @DeleteRowCount int
Select @DeleteRowCount = 2000
declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null)
declare @continue int, @rowCount int
select @continue = 1
while (@continue = 1)
begin
      begin tran
      insert into @DeletedAsyncRowsTable(AsyncOperationId)
      Select top (@DeleteRowCount) AsyncOperationId
      from AsyncOperationBase
      where OperationType in (1, 9, 12, 25, 27, 10) AND StateCode = 3 AND StatusCode in (30, 32)    
 
      Select @rowCount = 0
      Select @rowCount = count(*) from @DeletedAsyncRowsTable
      select @continue = case when @rowCount <= 0 then 0 else 1 end    
 
        if (@continue = 1)
        begin
            delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d
            where W.AsyncOperationId = d.AsyncOperationId
           
delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d
            where B.AsyncOperationId = d.AsyncOperationId
 
            delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
            where A.AsyncOperationId = d.AsyncOperationId           
 
            delete @DeletedAsyncRowsTable
      end
 
      commit
end

Step 3: Rebuild the index and set the recovery

 ALTER INDEX ALL ON AsyncOperationBase REBUILD WITH  
(FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)  

 – Rebuild Indexes & Update Statistics on WorkflowLogBase Table  
ALTER INDEX ALL ON WorkflowLogBase REBUILD WITH  
(FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)

ALTER DATABASE [DATABASE_NAME]
SET RECOVERY FULL
GO