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 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.
|State||Status||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 .
|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.|
|27||UpdateContractStates||Specifies an operation to update contract states.|
|30||ReindexAll||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,