CRM 4.0 Async table cleanup script

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

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, 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

–Drop the Index on AsyncOperationBase

DROP INDEX AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Workflow Elements by Aiden Kaskela

Business Solutions (CRM) MVP, Contractor, Evangelist

MSDYNAMICSBLOG BY DEEPESH

Real Experiences from Analyzing, Architecting, developing and working with Dynamics CRM, Dynamics 365 and sometimes .net

Mark Margolis's Blog

It's about Software, CRM, Product Development and sometimes Food.

GT // CRM

Gareth Tucker on Microsoft Dynamics CRM

Donna Edwards

Sharing my Journey with Dynamics 365

The WordPress.com Blog

The latest news on WordPress.com and the WordPress community.

%d bloggers like this: