Unable to delete *ANY* records in Dynamics CRM 2011

We had an odd issue this morning where a client was unable to delete any records on their CRM system whatsoever. When they tried, the generic ‘Unexpected Exception’ box popped-up.

Error[1]

The log-file wasn’t much use…

Generic SQL error.
[Microsoft.Crm.ObjectModel: Microsoft.Crm.Workflow.WorkflowNotificationPlugin]
  [ed1de875-76be-411e-8751-d439fa46287e: Notifies workflow that entity has been deleted.]

After more investigation, I found this in one of the trace files from around the same time…

Exception when executing query:
select "asyncoperation0".AsyncOperationId as "asyncoperationid",
"asyncoperation0".StateCode as "statecode",
"asyncoperation0".StatusCode as "statuscode",
"asyncoperation0".OperationType as "operationtype"
from
AsyncOperation as "asyncoperation0"
where
"asyncoperation0".RegardingObjectId = 'e80c61a3-c036-4238-9516-33aa88508166'
and "asyncoperation0".RegardingObjectTypeCode = 1071
and ("asyncoperation0".StateCode in (2, 0, 1))

Exception: System.Data.SqlClient.SqlException (0x80131904): The provided statistics stream is corrupt.

I was able to reproduce the error reliably by executing the above query in SSMS.

I’ve not really dealt with statistics in SQL before but they appear similar to indexes in configuration. I found I was unable to force an update to the statistics on the asyncoperation table – I recevied the “The provided statistics stream is corrupt” message.

update statistics asyncoperationbase

I then attempted to update each statistic in turn until I found the culprit.

update statistics asyncoperationbase fndx_RegardingObjectId_AsyncOperation

This was one of the two statistics against the RegardingObjectId column. When I attempted to update or delete this statistic specifically, I received an error like this…

Cannot DROP the index  because it is not a statistics collection

Eventually I discovered that there was both an index and a statistic with the same name. I tried dropping the index which was successful and also dropped the statistic of the same name automatically.

To resolve the problem, I used SSMS’s ‘Script as’ functionality to re-create the index. The associated statistic was added automatically.

Screenshot - 08_01_2013 , 11_38_54

“Update statistics” now worked for asyncoperation and the customer was able to delete records again.

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: