We recently deployed Microsoft SharePoint Office Server 2007 (MOSS) and ran into an issue with the Office SharePoint Search Service. The Search service was stuck in the "Crawling Full" state. After about 1 week of troubleshooting with Microsoft Product Support, we have resolved our problem. In a nutshell, our Database Maintenance plan included a task to rebuild indexes which had a side effect of removing the ability to allow duplicate keys in the index which SharePoint Search requires. The lesson I learned here is to not include any index maintenance as part of your database back strategy for SharePoint 2007. For more details and how to fix the problem, please read on.

Problem

The problem appeared when we first noticed that our default Content Source for SharePoint Search was stuck in the "Crawling Full" state. During normal operation, once a crawl is complete, the status should change back to Idle.

The problem occurred because our Database Maintenance plan included a Rebuild Index Task that did not preserve the duplicate keys in the index when it was being rebuilt:

Fix

To fix the issue, we had to recreate the 2 indexes that were impacted. One is in the Shared Services Search database and the other is in the WSS 3.0 Search database. Please exercise caution when making these changes and make sure to have database backups before your start.

1. Shared Services Search Database
   

   In the SharedServices Search Database, you must ensure that the IX_MSSAnchorPendingChangeLog index in the MSSAnchorPendingChangeLog table ignores duplicate values:

   

   If this is not enabled, you can follow the steps below to allow duplicate keys:

   1. In SQL Server Management Studio, navigate to the SSP Search database -> dbo.MSSAnchorPendingChangeLog -> IX_MSSAnchorPendingChangeLog index:

      

   2. Script out the index
   3. Delete the index
   4. Modify the SQL to set IGNORE_DUP_KEY = ON. Here is what our index script looked like (you will need to modify this script to use the appropriate database):

      -- TODO: change this to match the name of your SharedServices Search DB!
      USE [SharedServices_Search_DB]
      GO
      
      CREATE UNIQUE CLUSTERED INDEX [IX_MSSAnchorPendingChangeLog] ON [dbo].[MSSAnchorPendingChangeLog]
      ( 
          [CrawlId] ASC, 
          [TargetDocId] ASC
      )WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = ON, ONLINE = OFF) ON [PRIMARY]

2. WSS 3.0 Search Database

   In the WWS 3.0 Search Database, you must do the same as the SSP Search database and ensure that the IX_MSSAnchorPendingChangeLog index in the MSSAnchorPendingChangeLog table ignores duplicate values. If this is not enabled, you can follow the steps below:

   1. In SQL Server Management Studio, navigate to the WSS 3.0 Search database -> dbo.MSSAnchorPendingChangeLog -> IX_MSSAnchorPendingChangeLog index:

       

   2. Script out the index
   3. Delete the index
   4. Modify the SQL to set IGNORE_DUP_KEY = ON. Here is what our index script looked like:

      -- TODO: change this to match the name of your WSS Search Database Search DB!USE [WSS_Search_MOSSWEB01]
      GO
      
      CREATE UNIQUE CLUSTERED INDEX [IX_MSSAnchorPendingChangeLog] ON [dbo].[MSSAnchorPendingChangeLog]
      ( 
          [CrawlId] ASC, 
          [TargetDocId] ASC
      )WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = ON, ONLINE = OFF) ON [PRIMARY]

3. The final step, to ensure a clean Shared Services database, we created a new Shared Services Provider and removed the old one. We also removed the offending database maitenance plans.