CRM 4.0, Worflowlogbase y AsynOperationbase very large

11:55 |

Publicado por Killiam |

I am really sure, if you are working with Microsoft CRM 4.0 for few time, or you are fighting with it... and you open the database, with a name similar to Your-ORG_MSCRM, and you check this tables:

AsyncOperationBase
WorkflowLogBase

Look the properties, number of lines and also MB of these tables, sure you will have a fright.... I am sure they have a lot of lines, and also an important size in MB, I bet they do... The reasons for this:

The CRM 4.0 by default doesn´t delete the finished workflows, due to this in the Workflowlogbase table, a lot of unnecessary information is stored.

The Asyncoperationbase table, is used to save information of Microsoft CRM Asynchronous Processing Service (MSCRMAsyncService), which lead again in a lot of unnecessary information is stored.

The steps we will follow to solve the problem are the following:

The first step is ESSENTIAL, make a complete backup of CRM DATABASES

We will modify or add some keys in the register, to avoid finished Workflows to be stored in the database

We will run some scripts in the database that will empty Worflowlogbase and Asyncoperationbase tables

Well, lets work...

1-Backup

Well..., I think all of you Know how to make a SQLSERVER backup.... so I will jump this step... If you don´t know how to make a backup... I am not sure if you should continue readinf this article.... (well anyway if you google you will find how to make a backup easyly)

2-Windows Register Modification:

Go to this branch of the register Windows HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM

Check if the following key exists AsyncRemoveCompletedWorkflows, in case that exist change the value of this key to 1

In the case that this value doesn´t exist, we will create a DWORD key with the next name AsyncRemoveCompletedWorkflows and set the value of this register in 1

We wil execute an IISRESET in a MSDOS window, this will restart IIS services.

¿What are we doing setting the value of this register in 1 or adding the key AsyncRemoveCompletedWorkflows in the Windows register? By Default CRM 4.0 save in the databse all workflows, even those which had finished, for the majority of us this information is unusable, setting this register AsyncRemoveCompletedWorkflows to 1 no more finished workflows will be stored in the CRM database.

3-Running scripts to empty the lines of the Workflowlogbase and Asyncoperationbase.

Now in the CRM 4.0, no more finished workflows will be stored. But... how we should proceed to delete the information of those finished workflows that has been saved previously in Workflowlobase and Asyncoperationbase tables? , sure that there are hundreds, thousands, or probably millions of lines... don´t worry we have a solution for this... Remember that these scripts must be executed in the database which name is similar to this Your-ORG_MSCRM

Stop "Microsoft CRM Asynchronous Processing Service"

Second step we will create indexes to optimize the script execution:

CREATE NONCLUSTERED INDEX CRM_WorkflowLog_AsyncOperationID ON [dbo].[WorkflowLogBase] ([AsyncOperationID])
GO

CREATE NONCLUSTERED INDEX CRM_DuplicateRecord_AsyncOperationID ON [dbo].[DuplicateRecordBase] ([AsyncOperationID])

CREATE NONCLUSTERED INDEX CRM_BulkDeleteOperation_AsyncOperationID ON [dbo].[BulkDeleteOperationBase] (AsyncOperationID)
GO

Third step, update and rebuild the following indexes:

ALTER INDEX ALL ON AsyncOperationBase REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)

ALTER INDEX ALL ON WorkflowLogBase REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)

Fourth step, update all statystics involved in the queries...

UPDATE STATISTICS [dbo].[AsyncOperationBase] WITH FULLSCAN

UPDATE STATISTICS [dbo].[DuplicateRecordBase] WITH FULLSCAN

UPDATE STATISTICS [dbo].[BulkDeleteOperationBase] WITH FULLSCAN

UPDATE STATISTICS [dbo].[WorkflowCompletedScopeBase] WITH FULLSCAN

UPDATE STATISTICS [dbo].[WorkflowLogBase] WITH FULLSCAN

UPDATE STATISTICS [dbo].[WorkflowWaitSubscriptionBase] WITH FULLSCAN

Last step, this is the script that will delete the unusable information from this two tables:

IF EXISTS (SELECT name from sys.indexes

WHERE name = N'CRM_AsyncOperation_CleanupCompleted')

DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted

CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted

ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])

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

Una vez hecho esto ir a mirar el tamaño de estas dos tablas y veréis el resultado.....

Comentaros:

It is essential, follow the previous steps, before running the delete script. The create indixes, update statystics etc... if not, it will take a hundred years before finish the delete of the line... (I am talking for my own exprience, had to stop the script after more than some hours running)