Dynamics CRM: Failed to delete audit data from one or more of the specified partitions

  • Article

Issue

Exception information:

    Exception type: CrmException 

    Exception message: Failed  to delete audit data from one or more of the specified partitions.

The error in the event log is:

Failed to drop Partition  on AuditBase. Exception message ALTER TABLE SWITCH statement failed. There is no identical index in source table 'Orgname_MSCRM.dbo.AuditBase' for the index  'ndx_ObjectId' in target table 'Orgname_MSCRM.dbo.NonPartitionAuditTable' .

Cause

You will see this error if there is a mismatch in the structure of 'ndx_ObjectId'  index from what is shipped OOB. You will have to revert it back to the OOB structure for delete partition to succeed. 

The stored procedure creates a temporary non-partitioned table (to switch the oldest audit partition) with all OOB index schemas hard-coded. If there are any additional indexes added/ columns on existing indexes modified, the partition switching step will fail.

Solution

Disclaimer: Take a backup of your CRM databases before making any changes 

Script out the ndx_ObjectId index from the environment  and compare it to the index definition shipped OOB

CREATE  NONCLUSTERED INDEX [ndx_ObjectId]  ON [dbo].[AuditBase] 

( 

      [ObjectId]  ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 

GO

Drop the Index and recreate it