CRM in-line search fails with SQL error
After upgrading Microsoft CRM from earlier versions we found that the global search function when enabled failed to return any results, and once the index for the global search had run over a 24-hour period, the in-line search function for any entity would cause a crash and SQL error message to be displayed on page.
In our particular instance this CRM environment had been upgraded from much earlier versions of CRM and included an attempt to solve some upgrade issues by dropping indexes. Initially our thoughts were that the dropping of the indexes were responsible for the problems. However it appears retrospectively that was a fragmentation of indexes that cause the issue. I cannot be exactly sure why the maintenance procedure that is run on the SQL Server did not rebuild and reorganise the indexes sufficiently that the global social function. However the following solution did work for us.
We had pretty much followed the recommendation of this discussion forum.
After submitting a support ticket to Microsoft they requested us to:
- Run following command on CRM database to check fragmentation percentage:
SELECT object_id AS ObjectID, index_id AS IndexID, avg_fragmentation_in_percent AS PercentFragment,
fragment_count AS TotalFrags, avg_fragment_size_in_pages AS PagesPerFrag, page_count AS NumPages
FROM sys.dm_db_index_physical_stats(DB_ID(”), NULL, NULL, NULL , ‘DETAILED’) WHERE avg_fragmentation_in_percent > 0 ORDER BY ObjectID, IndexID
- In case the fragmentation percent is more than 25-30% we have to rebuild the indexes.
the reference provided by Microsoft was helpful, but not as helpful as we would have liked. We ended up running the following query that automatically rebuilt all the indexes.
After doing this, we were then able to turn on the global search and weight the relevant period of time for it to complete the indexing. It appears to have fixed our problem with both global search returning valid results, and in-line search no longer broken when global search was unable.