Category Archives: CRM4

CRM 4.0 Deleting Workflows, AsyncOperations directly from SQL server

We had some performance problems with one of our client using CRM 4.0 due to the high level of jobs/Workflows executing at the same time on their system. We end up with several deathlock problems where some workflows were Suspended waiting for resources that other suspended workflows where keeping.

To resolve this issue we decided to delete all the workflows with suspended Status. The problem deleting this records from the “AsyncOperationBase ” datatable using SQL server is the relationships that this records have with the following tables

  1. WorkflowLogBase
  2. BulkDeleteFailureBase
  3.  WorkflowWaitSubscriptionBase.

To solve the problem we created a SQL Transaction were we use a table to keep all the AsyncOperationBaseIDs of all the records we wanted to delete, then delete all the related records in the “WorkflowLogBase,BulkDeleteFailureBase and WorkflowWaitSubscriptionBase” tables to finally be able to delete those record on the “AsyncOperationBase ” datatable. Everything wrap in a transaction that will rollback in case of error.

The SQL will look like this.

-- we First create some indexes to speed up the execution
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
-- Records in the AsyncOpetion table have relationship with records in WorkflowLogBase,BulkDeleteFailureBase and WorkflowWaitSubscriptionBase
declare @DeleteRowCount int
Select @DeleteRowCount = 2000
--We Create Temp table to keep all the AsyncOperationId from  AsyncOperationBase that we want to delete
declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)
declare @continue int, @rowCount int
select @continue = 1
while (@continue = 1)
begin
begin tran
--Fill up the temp table with the query we want. In this case we select AsyncOperations with name NMYPROCESS and
-- StatusCode = 10 Suspended. And only the records of the following Types 1,9,12,25,27,10 (Microsoft Recomendation)
insert into @DeletedAsyncRowsTable(AsyncOperationId)
      Select top (@DeleteRowCount) AsyncOperationId from AsyncOperationBase
      where Name like '%MYPROCESS' -- Name like MyPROCESS
	  and StatusCode in (10) -- Suspended
      and OperationType in (1, 9, 12, 25, 27, 10)
-- Microsoft recomends deleting only this Operation types:Event, CollectSqmData,Workflows, PersisMatchCode,FullTextCatalogIndex ,UpdateContractStates
       Select @rowCount = 0
      Select @rowCount = count(*) from @DeletedAsyncRowsTable
      select @continue = case when @rowCount <= 0 then 0 else 1 end
        if (@continue = 1)        begin
            -- deleting from WorkflowLogBase
            delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d
            where W.AsyncOperationId = d.AsyncOperationId
             -- deleting from BulkDeleteFailureBase
            delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d
            where B.AsyncOperationId = d.AsyncOperationId
             -- deleting from WorkflowWaitSubscriptionBase
delete WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d
where WS.AsyncOperationId = d.AsyncOperationID
   -- Finaly deleting from AsyncOperationBase
            delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
            where A.AsyncOperationId = d.AsyncOperationId
            /*Delete the temp table*/
            delete @DeletedAsyncRowsTable
end
-- If error rollback otherwise commit.
IF @@ERROR <> 0
   BEGIN
      ROLLBACK TRAN
   END
  ELSE
  BEGIN
    COMMIT TRAN
  END

end
--Drop the Index on AsyncOperationBase
DROP INDEX AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted

If instead deleting Suspended Jobs you want to delete job in other states the following table describes all the StatusCode Values in SQL.

StatusCode Values

State Status Numeric status value in SQL
Ready WaitingForResources 0
Suspended Waiting 10
Locked InProgress 20
Locked Pausing 21
Locked Canceling 22
Completed Succeeded 30
Completed Failed 31
Completed Canceled 32

In the other hand, If you want to delete othe kind of Async Operations this table shows all the OperationType values .

Value Field Description
1 Event Specifies a system event.
2 BulkEmail Specifies a bulk e-mail operation.
3 Parse Specifies an import file parse operation.
4 Transform Specifies an operation to transform parse data.
5 Import Specifies an import operation.
6 ActivityPropagation Specifies a campaign activity propagation operation.
7 PublishDuplicateRule Specifies an operation to publish a duplicate detection rule.
8 BulkDetectDuplicates Specifies a bulk duplicate detection operation.
9 CollectSqmData Specifies an operation to collect SQM data.
10 Workflow Specifies a workflow operation.
11 QuickCampaign Specifies a quick campaign operation.
12 PersistMatchCode Specifies an operation to update the matchcode.
13 BulkDelete Specifies a bulk delete operation.
14 DeletionService Specifies a Deletion Service operation.
15 IndexManagement Specifies an index management operation.
16 CollectOrgStats Specifies an operation to collect an organization’s statistics.
17 ImportingFile Specifies an import subprocess operation.
18 CalculateOrgStorageSize Specifies an operation to calculate an organization’s storage size.
19 CollectOrgDBStats Specifies an operation to collect an organization’s database statistics.
20 CollectOrgSizeStats Specifies an operation to collection an organization’s size statistics.
21 DatabaseTuning Specifies an operation for database tuning.
22 CalculateOrgMaxStorageSize Specifies an operation to calculate an organization’s maximum storage size.
23 BulkDeleteChild Specifies a bulk delete subprocess operation.
24 UpdateStatisticIntervals Specifies an operation to update statistic intervals.
25 FullTextCatalogIndex Specifies an operation to generate an organization full text catalog index.
26 DatabaseLogBackup
27 UpdateContractStates Specifies an operation to update contract states.
28 ShrinkDatabase
29 ShrinkLogFile
30 ReindexAll Specifies an index management operation.
31 StorageLimitNotification
32 CleanupInactiveWorkflowAssemblies
38 ImportSampleData

If you want more info about using Transactions in SQL a great post about it here

I hope it was usefull,

Have a nice day,

Hache