The Microsoft CRM Deletion Service does not remove records from the Microsoft CRM SQL Server database promptly (831189)

SYMPTOMS

When you delete an activity in Microsoft Business Solutions CRM (Microsoft CRM) 1.0, if that activity has an attachment, the Microsoft CRM Deletion Service cannot delete the attachment. You may see the following symptoms when this problem occurs:

CPU utilization increases on the Microsoft SQL Server-based server that contains the Microsoft CRM databases.
Performance decreases on the server that is running Microsoft CRM and on the SQL Server-based server that contains the Microsoft CRM databases.

Note When you turn off the Deletion Service, CPU utilization and performance return to typical levels.

CAUSE

When a Microsoft CRM e-mail activity includes an attachment, an associated record exists in the ActivityMimeAttachment table. This table is stored on a SQL Server-based server in the Organization_Name_MSCRM database. When you delete the activity, the attachment is not marked for deletion in the ActivityMimeAttachment table, and the Deletion Service cannot delete the record. Therefore, the Deletion Service continues to run but does not remove the records. When this problem occurs, the CPU utilization remains at a high level on the SQL Server-based server that contains the Microsoft CRM database.

RESOLUTION

To resolve this problem, apply the Microsoft CRM hotfix rollup for KB articles 831181, KB 831189, and KB 834548. For additional information and to obtain this hotfix rollup, click the following article number to view the article in the Microsoft Knowledge Base:

834572 Microsoft Business Solutions CRM hotfix rollup for KB articles 831181, KB 831189, and KB 834548

Note Before you install this hotfix rollup, you must modify the ActivityMimeAttachment records in the Organization_Name_MSCRM database by running a SQL statement. To modify these records, follow these steps:

Back up the Microsoft CRM SQL Server databases and Active Directory as indicated in the Microsoft CRM Implementation Guide.

Run the following SQL statement in SQL Query Analyzer against the Organization_Name_MSCRM database.

DELETE FROM ActivityMimeAttachment WHERE ActivityId IN 
(SELECT ActivityId FROM ActivityBase WHERE DeletionStateCode = 2)

You only have to run this SQL statement one time. This SQL statement removes any activities and associated attachments that were previously marked for deletion.

MORE INFORMATION

You can also increase performance by adding indexes to the WFActionLog table and to the WFRuleLog table in the Microsoft CRM SQL Server database.

Note You must be a Microsoft SQL Server administrator to add these indexes.

To add these indexes, follow these steps:

Back up the Microsoft CRM SQL Server databases and Active Directory as indicated in the Microsoft CRM Implementation Guide.
Run SQL Query Analyzer on the SQL Server-based server that contains the Microsoft CRM databases.
Connect to the SQL Server-based server that contains the Microsoft CRM databases. You can use either Windows authentication or SQL Server authentication to connect.
In the list of databases, click Organization_Name_MSCRM, where Organization_Name is the name of your licensed organization for Microsoft CRM.
To add a new index to the WFActionLog table, run the following SQL statement in SQL Query Analyzer against the Organization_Name_MSCRM database.
```
Create index ndx_wfactionlog_processinstanceid on WFActionLog(ProcessInstanceId)
```
To add a new index to the WFRuleLog table, run the following SQL statement in SQL Query Analyzer against the Organization_Name_MSCRM database.
```
Create index ndx_wfrulelog_processinstanceid on WFRuleLog(ProcessInstanceId)
```
To reindex the Microsoft CRM database tables, run the DBCC DBREINDEX script against the Organization_Name_MSCRM database. This SQL script makes sure that the SQL statements that run against these tables can use the new indexes that are based on current SQL Server data.

To obtain the DBCC DBREINDEX script, visit the following Microsoft Web site:
Optimizing the Performance of Microsoft CRM 1.2 White Paper
http://www.microsoft.com/downloads/details.aspx?FamilyID=e1a00070-2e5a-410a-881f-d859f8474933&DisplayLang=en

The DBCC DBREINDEX script appears in the "Reindex the Database Using DBCC DBREINDEX Script" section of this white paper.