Twitter Image

Manually Reindexing a MSCRM Database

Written by Stéphane Dorrekens
Wednesday, 07 August 2013 09:52

We recently faced an issue where the automatic reindexing job was failing with a timeout 
<MSCRMAsyncService$maintenance. Job Scheduler has executed tasktype=30, organizationid=, starttime=6/08/2013 22:29:22, endtime=6/08/2012 22:40:22 PM, resultcode=1, errormessage=System.Data.SqlClient.SqlException (0x80131904):Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

This timeout at 600sec seems to be hardcoded in the async maintenance service both in the oledb connection and in the stored procedure (the actual statement being executed is p_reindexall 1,600 with 600 the max seconds). After reading this blog article from the Microsoft Dynamics CRM PFE team, it seems the preferred option is to handle this job in an Sql maintenance plan. As this procedure was not yet documented, I thought it would be useful to do it here.

First, start the SQL Server Management Studio and Connect it to the SQL Server instance hosting the Mscrm database you want to Manually Reindex, then Go the SQL Server Agent/Jobs and Right Click to get New Job

Then in the General Tab, Specify the Name of the Job and the Owner (usually the same service account which runs the MSCRMAsync Maintenance Service)

Then Click on Steps..

and specify a name, the actual MSCRM database you want to reindex and enter the following command : exec p_reindexall 1,<Number of seconds for timeout>.
Nb: here's the full list of parameters you can use in this stored procedure

  • AllIndexTypes, Default=0; 0=Clustered Index only, 1=Clustered and Non Clustered indexes
  • MaxRunTime, NO Default;  Maximum allowed running time (in seconds)
  • FragRebuildPct, Default=30;  Percentage of fragmentation at which indexes are rebuilt
  • MinPages, Default=25; Avoid tables less than MinPages amount of pages  
  • Verbose, Default=0; 1=Print progress messages and detailed results


Then click on schedules and specify the running times

Go back to the General Tab, verify that the Job is enabled and click OK.

The Last point is that you need to disable the job in the normal MSCRM Async Service Maintenance. To do this, download the CRM 2011 Maintenance Job Editor, install it in your \Program Files\Microsoft Dynamics CRM\Tools directory and start CRM2011JobEditor (note that there's a post UR12 and pre UR12 version). Then select the Organization for which you enabled the manual reindexing, select the "Reindex All" Job and Change the next Run date to something far in the future (ie: 2099)