Hi,
There is a KB Article for this.
https://support.microsoft.com/en-us/kb/968520?wa=wsignin1.0
I have taken the same and added a condition to only the records of the previous month.
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],[completedon])
GO
-- StateCode 3 = Completed, StautsCode = 30 Succeeded, 31 Failed and 32 Cancelled
--while(1=1)
begin
declare @DeleteRowCount int = 2
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 (10)
AND StateCode = 3
/*add in StatusCode of 31 if you also want to delete failure records*/
AND StatusCode in (30, 32)
AND completedon < DATEADD(MONTH, -1, GETDATE())
select @rowsAffected = @@rowcount
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
Regards.
Gopinath
http://mscrmtechie.blogspot.in/
