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
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
CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted
ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])
-- 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)
--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*/
-- If error rollback otherwise commit.
IF @@ERROR <> 0
--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.
||Numeric status value in SQL
In the other hand, If you want to delete othe kind of Async Operations this table shows all the OperationType values .
||Specifies a system event.
||Specifies a bulk e-mail operation.
||Specifies an import file parse operation.
||Specifies an operation to transform parse data.
||Specifies an import operation.
||Specifies a campaign activity propagation operation.
||Specifies an operation to publish a duplicate detection rule.
||Specifies a bulk duplicate detection operation.
||Specifies an operation to collect SQM data.
||Specifies a workflow operation.
||Specifies a quick campaign operation.
||Specifies an operation to update the matchcode.
||Specifies a bulk delete operation.
||Specifies a Deletion Service operation.
||Specifies an index management operation.
||Specifies an operation to collect an organization’s statistics.
||Specifies an import subprocess operation.
||Specifies an operation to calculate an organization’s storage size.
||Specifies an operation to collect an organization’s database statistics.
||Specifies an operation to collection an organization’s size statistics.
||Specifies an operation for database tuning.
||Specifies an operation to calculate an organization’s maximum storage size.
||Specifies a bulk delete subprocess operation.
||Specifies an operation to update statistic intervals.
||Specifies an operation to generate an organization full text catalog index.
||Specifies an operation to update contract states.
||Specifies an index management operation.
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,