Quite a while back had some issues upgrading a CRM instance. It turned out the amount of ASync operations during the lifetime of the CRM application had generated quite a lot of entries. (millions). For some reason I had a hard time find the root cause of this, which turned out to be a lot more general than just relating to an upgrade. I rarely see CRM installations where a clean up job is created, so most CRM installations will actually suffer from large amount of async operations stored.
Have a look at your AsyncOperationBase table and see how many entries are there. You might be surprised.
Below are the SQL lines I ran/set up as a maintenance plan in order to reduce the number. Depending on the number of records, it might be necessary to run the scripts a few times.
Code is from referenced KB article: http://support.microsoft.com/kb/968520/en-us
IF EXISTS (SELECT name from sys.indexes WHERE name = N'CRM_AsyncOperation_CleanupCompleted') DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted GO CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType]) GO while(1=1) begin declare @DeleteRowCount int = 10000 declare @rowsAffected int declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key) 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) /*AND CompletedOn <= DATEADD(mm, -1, GETDATE())*/ select @rowsAffected = @@rowcount delete poa from PrincipalObjectAccess poa join WorkflowLogBase wlb on poa.ObjectId = wlb.WorkflowLogId join @DeletedAsyncRowsTable dart on wlb.AsyncOperationId = dart.AsyncOperationId 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 WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d where WS.AsyncOperationId = d.AsyncOperationID delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d where A.AsyncOperationId = d.AsyncOperationId /*If not calling from a SQL job, use the WAITFOR DELAY*/ if(@DeleteRowCount > @rowsAffected) return else WAITFOR DELAY '00:00:02.000' end
1: Support article code was updated for a more smooth execution.
2: Added required retention time on records of 1 month;