Hi every body!
On the week I faced with problem when my MS CRM 2011 has been working very slow. And I investigate what can be reason and found that, the AsyncOperationBase table has 25 Gb in SQL !
How can resolve this issue you can see in knowledge base the KB968520 by Microsoft. I hope this help you.
I coped this KB in this blog from http://support.microsoft.com/kb/968520, sorry Microsoft =)
If recurring jobs were canceled, they will be removed.
Notes
To determine the number of records to be deleted by the script in this article, run the following count script against the OrganizationName_MSCRM database:
Script error
When you run the cleanup script, you may receive an error message that resembles the following:
If you receive this error message, stop the cleanup script, and then follow these steps to remove the remaining WorkflowWaitSubscription records that exist for completed or canceled workflows. These records should no longer exist, because they should have been deleted when the workflows were completed or canceled. You should not see any records that are returned from this query. Anything left in the WorkflowWaitSubscriptionBase table that appears in this query is an orphaned record. You cannot delete these records through the UI because the Microsoft CRM Async process is in a canceled or completed state.
The following script will verify how many orphaned WorkflowWaitSubscriptionBase records exist for completed and canceled workflow records:
The following script will delete WorkflowWaitSubscriptionBase records for stranded WorkflowWaitSubscriptionBase records for completed and canceled workflow records:
After this delete statement is executed, the AsyncoperationBase and Workflow cleanup script will complete successfully.
On the week I faced with problem when my MS CRM 2011 has been working very slow. And I investigate what can be reason and found that, the AsyncOperationBase table has 25 Gb in SQL !
How can resolve this issue you can see in knowledge base the KB968520 by Microsoft. I hope this help you.
I coped this KB in this blog from http://support.microsoft.com/kb/968520, sorry Microsoft =)
Performance is slow if the AsyncOperationBase table becomes too large in Microsoft Dynamics CRM
Symptoms.
When you run Microsoft Dynamics CRM 4.0 or Microsoft Dynamics CRM 2011,
the AsyncOperationBase table grows to be very large. When the table
contains millions of records, performance is slow.
Additionally, errors that resemble the following are logged in the application event log on the server that is running Microsoft .
Additionally, errors that resemble the following are logged in the application event log on the server that is running Microsoft .
Resolution
To resolve this problem, perform a cleanup of the AsyncOperationBase table by running the following script against the<OrgName>_MSCRM database, where the placeholder<OrgName> represents the actual name of your organization.
Warning Before you clean up the data, be aware that completed system jobs have business value in some cases and have to be stored for a long period. Therefore, you should discuss this with your organization's administration staff first.
System jobs that are affected:
Warning Before you clean up the data, be aware that completed system jobs have business value in some cases and have to be stored for a long period. Therefore, you should discuss this with your organization's administration staff first.
System jobs that are affected:
- SQM data collection. Software Quality Metrics collects data for the customer experience program.
- Update Contract States SQL job. This job runs one time per day at midnight. This job sets the expired contracts to a state of Expired.
- Organization Full Text Catalog Index. Populates full text index in db for searching Microsoft Knowledge Base articles in CRM.
If recurring jobs were canceled, they will be removed.
Notes
- For Microsoft Dynamics CRM 4.0 The SQL script in this Knowledge Base article is a one-time effort only. You can add this as a SQL job to run on a recurring nightly, weekly, or monthly basis. As your CRM runs, you have to either apply this article weekly, depending on your business needs, or apply the solution by writing custom BULK DELETE jobs. (Refer to our CRM SDK documentation on the BulkDeleteRequest.QuerySet property, on the BulkDeleteRequest class, and on the order of deletion).
- For Microsoft Dynamics CRM 2011 The SQL script in this Knowledge Base article is a one-time effort only. You can add this as a SQL job to run on a recurring nightly, weekly, or monthly basis. As your CRM runs, you have to either apply this article weekly, depending on your business needs, or apply the solution by using BULK DELETE jobs by defining a job by using the BULK DELETE wizard.
- Make sure that the AsyncOperation records for workflows and the corresponding records are deleted from the WorkflowLogBase object.
- Make sure that all the corresponding bulkdeletefailure records are deleted.
- Make sure that only the following Async operation types are
deleted if the state code of the types is 3 and the status code of the
types is 30 or 32:
- Workflow Expansion Task (1)
- Collect SQM data (9)
- PersistMatchCode (12)
- FullTextCatalogIndex (25)
- UpdateContractStates (27)
- Workflow (10)
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 WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d
where WS.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
Improving the performance of the deletion script
- To improve overall Microsoft Dynamics CRM performance, schedule the Microsoft Dynamics CRM Deletion Service to run during off-peak hours for Microsoft Dynamics CRM. By default, the service runs at the time that Microsoft Dynamics CRM was installed. However, you can set the service to run at 10:00 PM instead of at the default time. To do this, use the Microsoft Dynamics CRM ScaleGroup Job Editor. For more information, visit the following Microsoft Developer Network (MSDN) website: Note This action does not directly affect the performance of the script.
- To
improve the performance of the deletion scripts in this article and to
improve the Microsoft Dynamics CRM Deletion Service code that runs
similar deletions, add the following three indexes to the
OrganizationName_MSCRM database before you run the deletion script in
this article:
Note If you do not add these indexes, the deletion script may take hours to run.
CREATE NONCLUSTERED INDEX CRM_WorkflowLog_AsyncOperationID ON [dbo].[WorkflowLogBase] ([AsyncOperationID]) GO CREATE NONCLUSTERED INDEX CRM_DuplicateRecord_AsyncOperationID ON [dbo].[DuplicateRecordBase] ([AsyncOperationID]) GO CREATE NONCLUSTERED INDEX CRM_BulkDeleteOperation_AsyncOperationID ON [dbo].[BulkDeleteOperationBase] (AsyncOperationID) GO - Stop the Microsoft Dynamics CRM Asynchronous Processing Service while you run this script.
- Optional Rebuild the following indexes and update statistics:
-- Rebuild Indexes & Update Statistics on AsyncOperationBase Table ALTER INDEX ALL ON AsyncOperationBase REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF) GO -- Rebuild Indexes & Update Statistics on WorkflowLogBase Table ALTER INDEX ALL ON WorkflowLogBase REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF) GO - Optional Update Statistics
with Full Scan on all the tables that are involved with this query by
using the following commands (preferably at off peak hours):
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 - Optional Change the MSCRM database's recovery model to Simple to avoid excess generation of Microsoft SQL Server logs. For SQL Server 2005, log on to the Microsoft SQL Server Management Studio as Administrator, right-click your <org_name>_MSCRM database, click Properties, click Options, and then click Recovery Model. Mark Simple, and then click OK. After you run this script the first time, the <org_name>_MSCRM database recovery model should be switched back to FULL for the best data-recoverability model.
To determine the number of records to be deleted by the script in this article, run the following count script against the OrganizationName_MSCRM database:
Select Count(AsyncOperationId)from AsyncOperationBase WITH (NOLOCK)
where OperationType in (1, 9, 12, 25, 27, 10)
AND StateCode = 3 AND StatusCode IN (30,32)
Script error
When you run the cleanup script, you may receive an error message that resembles the following:
The
DELETE statement conflicted with the REFERENCE constraint
"asyncoperation_workflowwaitsubscription". The conflict occurred in
database "Contoso_MSCRM", table "dbo.WorkflowWaitSubscriptionBase",
column 'AsyncOperationId'.The statement has been terminated.
If you receive this error message, stop the cleanup script, and then follow these steps to remove the remaining WorkflowWaitSubscription records that exist for completed or canceled workflows. These records should no longer exist, because they should have been deleted when the workflows were completed or canceled. You should not see any records that are returned from this query. Anything left in the WorkflowWaitSubscriptionBase table that appears in this query is an orphaned record. You cannot delete these records through the UI because the Microsoft CRM Async process is in a canceled or completed state.
The following script will verify how many orphaned WorkflowWaitSubscriptionBase records exist for completed and canceled workflow records:
select count(*) from workflowwaitsubscriptionbase WITH (NOLOCK)
where asyncoperationid in
(Select asyncoperationid from AsyncOperationBase WITH (NOLOCK)
where OperationType in (1, 9, 12, 25, 27, 10)
AND StateCode = 3 AND StatusCode IN (30,32))
The following script will delete WorkflowWaitSubscriptionBase records for stranded WorkflowWaitSubscriptionBase records for completed and canceled workflow records:
delete from workflowwaitsubscriptionbase
where asyncoperationid in(Select asyncoperationidfrom AsyncOperationBase
where OperationType in (1, 9, 12, 25, 27, 10)
AND StateCode = 3 AND StatusCode IN (30,32))
