Slow Performance or Large Database File in MS CRM AsyncOperationBase

 

There is a lot of information online about the MS CRM performance issues and very large database file sizes. Without going into MS CRM (you can Google that) it is by design. Can you believe that!

CRM is set to record everything that is going on, and for most people, most of the transaction logging in the AsyncOperationBase table is just junk. Now you would expect that there is a little in application sort of… clean up your junk… feature in CRM, but alas we are dreaming of some Apple product. For this reason, the database has to be manually cleared, or an auto process set to do this manually.

So the situation before me now is a massive database of 20GB with almost 17 million transactions listed.

This server I am looking at is Windows 2008, and the SQL server is also Windows 2008.

These are the steps to fix this problem in this case.

1. Log in to SQL Management Studio with an administrator account that has full access to the database in question.

2. The database will loos like ORGNAME_MSCRM and you right click and select New Query

image

3. Copy and paste the script below into the SQL Query window and select the Execute Button (that does not look like a button till you hover)


Select Count(*) from AsyncOperationBase

image

You will end up with a query result that show the number of records that will need to be addressed, this will give you a good indication of the time it will take. 17 million = a long time!

Because of this big number, I am going to take some steps to improve the performance that this query will run.

4. Backup the database to ensure you have a safe starting point.

5. Shrink the Database by right clicking and selecting tasks, shrink, database:

image

Then OK on the default settings.

6 Then improve performance by creating some indexes for the cleanup query. Paste this code over the Query Code in step 3, and execute.


CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted
ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])
GO

CREATE NONCLUSTERED INDEX CRM_WorkflowLog_AsyncOperationID ON [dbo].[WorkflowLogBase] ([AsyncOperationID])
GO 

CREATE NONCLUSTERED INDEX CRM_DuplicateRecord_AsyncOperationID ON [dbo].[DuplicateRecordBase] ([AsyncOperationID])
GO

CREATE NONCLUSTERED INDEX CRM_BulkDeleteOperation_AsyncOperationID ON [dbo].[BulkDeleteOperationBase]
(AsyncOperationID)
GO

 

image

This little baby may spin for some time: image

7. Stop the Microsoft CRM Asynchronous Processing Service in the Server Services, found under the Administrator tools / Services

image

8. Set Simple Database Recovery. Right click the database and select Properties / Options / Recovery Model to simple

image

9. Run this command in the SQL Query, like in step 3


-- Rebuild Indexes & Update Statistics on AsyncOperationBase Table 
ALTER INDEX ALL ON AsyncOperationBase REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)
GO 
-- 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)

GO

You will probably see your database almost double in size with this one.

10. Then finally run the command that will clean up the database


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

Executing just as we did in step 3.

11. Reset the database to Full Recovery Mode, reversing the step we took in step 8.

12. Shrink the database again exactly as we did in step 5.

13. Start the Service again, reversing step 7.

And you should be good to go. More information on this and DotNetNuke Modules.

5 thoughts on “Slow Performance or Large Database File in MS CRM AsyncOperationBase

  1. Pingback: InteractiveWebs Blog » Scheduling a CRM System Job to Delete AsyncOperationBase junk from CRM 4.0 with CRM Automatic AsyncOperationBase Delete Tool

  2. Thanks. This is just what the doctor ordered. I finally have a procedure that will help me manage the size of the async and workflowlog tables.

    I have used the above procedures and they work great (after a small tweak to a SQL comment).

    Its great that I can use SQL rather than the SDK

    I really don’t know why Msoft don’t provide this facility within CRM

    Thanks for sharing this with us all.

    Joe

Leave a Reply