Monday 28 April 2014

Clean Up AsyncOperation(System Jobs) Table to Improve CRM Performance.

Today i am going to explain SQL Procedure Steps to cleanup MSCRM 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

1 comment:

  1. I did that, still the size not decreasing!! is it safe to drop the table and recreate ?

    ReplyDelete

Blogger Widgets