Today i am going to explain SQL Procedure Steps to cleanup MSCRM AsyncOperation table :
Asynchronous Operation States :
Step 1 : Create Index on specific column of AsyncOperation Table
Step 2 : Delete all Succeeded, Failed and Canceled Jobs
Step 3 : Drop the Index on AsyncOperation Table
Asynchronous Operation States :
State
|
Status
|
Numeric status value
|
Ready
|
WaitingForResources
|
0
|
Suspended
|
Waiting
|
10
|
Locked
|
InProgress
|
20
|
Locked
|
Pausing
|
21
|
Locked
|
Canceling
|
22
|
Completed
|
Succeeded
|
30
|
Completed
|
Failed
|
31
|
Completed
|
Canceled
|
32
|
Step 1 : Create Index on specific column of AsyncOperation Table
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
Step 2 : Delete all Succeeded, Failed and Canceled Jobs
declare @DeleteRowCount int Select @DeleteRowCount = 2000 declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key) 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,31,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 WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d where WS.AsyncOperationId = d.AsyncOperationID delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d where A.AsyncOperationId = d.AsyncOperationId delete @DeletedAsyncRowsTable end commit end
Step 3 : Drop the Index on AsyncOperation Table
DROP INDEX AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted
I did that, still the size not decreasing!! is it safe to drop the table and recreate ?
ReplyDelete