Consider the following scenario. You run Microsoft Office SharePoint Portal Server 2003 or Microsoft Office SharePoint Server 2007 in a server farm deployment that uses Microsoft SQL Server 2005 as the database server. You use the Maintenance Plan Wizard in SQL Server Management Studio to create maintenance plans that rebuild indexes.

In this scenario, you experience issues with portal alerts in SharePoint Portal Server 2003 or with search performance in SharePoint Server 2007. For more information about the specific symptoms that you experience, see the "More Information" section.

Back to the top

These issues occur because the Rebuild index task does not restore all the options that are set on the indexes that are used by SharePoint Portal Server 2003 or by SharePoint Server 2007 after indexes are rebuilt. The Ignore duplicate values option is turned off after indexes are rebuilt.

Note This problem has been corrected in SQL Server 2005 Service Pack 2. If you have to re-enable the Rebuild Index task, update SQL Server.

Back to the top

To resolve these issues, disable the maintenance plan that contains the Rebuild Index task. Then, use SQL Server Management Studio to locate the indexes that are specified in the following table, as appropriate for your situation. For each index, make sure that the Ignore duplicate values check box is selected.

Note You may have to temporarily click to clear one of the following check boxes before you can modify the index settings:

•	The Unique check box on the General tab on the Index Properties page
•	The Ignore duplicate values check box on the Options page

After you modify the index settings, make sure that you click to select the Unique check box again.

SharePoint Portal Server 2003

Database	Table name	Index name
_Serv	Srch_docspecialprops	IX_CatIdDocId
_Serv	Sub_PropVersions	IX_srch_propVersions_non_Clustered
_Serv	SupportedProps	IX_Unique_Uri
_Serv	URIHash	IX_URIs
_Prof	ProfileImport_copy	IX_ProfileImport_copy

SharePoint Server 2007

Database	Table name	Index name
Search	MSSAlertDocHistory	IX_AlertDocHistory
Search	MSSAnchorChangeLog	IX_MSSAnchorChangeLog
Search	MSSAnchorPendingChangeLog	IX_MSSAnchorPendingChangeLog
Search	MSSCrawlChangedSourceDocs	IX_MSSCrawlChangedSourceDocs
Search	MSSCrawlChangedTargetDocs	IX_MSSCrawlChangedTargetDocs
Search	MSSCrawledPropSamples	IX_MSSCrawledPropSamplesByDocid
Search	MSSCrawlErrorList	IX_MSSCrawlErrorList_hrResult
Search	MSSCrawlHostList	IX_MSSCrawlHostList_Name
Search	MSSCrawlQueue	IX_MSSCrawlQueue
Search	MSSDocSdids	IX_MSSDocSdids

Before you re-enable the maintenance plan, delete the Rebuild Index task or replace the Rebuild Index task with an Execute Transact-SQL Statement task. The Execute Transact-SQL Statement task should restore all options on indexes.

You can run the following SQL queries to determine whether the "Ignore Duplicate Values" option is set correctly on these specific indexes.

•

In SharePoint Server 2007, run the followng query in the appropriate search database. For example, run this query in SharedServices1_Search_DB. select [name], [type_desc], [is_unique], [ignore_dup_key] from sys.indexes where name in ('IX_MSSAnchorChangeLog', 'IX_MSSAnchorPendingChangeLog', 'IX_MSSCrawlChangedSourceDocs', 'IX_MSSCrawlChangedTargetDocs','IX_MSSCrawledPropSamplesByDocid','IX_MSSCrawlErrorList_hrResult', 'IX_MSSCrawlHostList_Name','IX_MSSCrawlQueue','IX_MSSDocSdids')

•

In SharePoint Portal Server 2003, run the following query from the _SERV database. select [name], [type_desc], [is_unique], [ignore_dup_key] from sys.indexes where name in ('IX_CatIdDocId', 'IX_srch_propVersions_non_Clustered', 'IX_Unique_Uri', 'IX_URIs', 'IX_ProfileImport_copy')

The Ignore_duplicate_key value for each index should be 1. If the server reports a 0 (zero) for the Ignore_duplicate_key value, you are experiencing this issue.

Back to the top

Symptoms that you experience in SharePoint Portal Server 2003

If you are running SharePoint Portal Server 2003, users are not notified when items in the portal site are modified. Additionally, you may experience the following symptoms:

•	Users receive an e-mail that confirms that a message was created. However, users do not receive later e-mail messages when items are modified.
•	The Spsnotificationservice.log file contains the following error message: Exception information: Microsoft.SharePoint.Portal.Alerts.NotificationDataTemporarilyUnavailableException: Failed to generate notification:The DocSpecialProps data may not be ready yet. Retry.
•	The Dbo.srch_docspecialprops table in the _Serv database is empty.
•	A SQL profiler trace that is taken during a crawl contains the following SQL Server 2601 error message: "Cannot insert duplicate key row in object" This error message occurs when data is inserted into the Srch_docspecialprops table.

Back to the top

Symptoms that you experience in SharePoint Server 2007

If you are running SharePoint Server 2007, the indexing status remains in the Crawling state.

If the indexes are reset, and if a full crawl is started, you may also experience the following symptoms:

•	The number of handles that are opened by the MSSearch.exe process increases.
•	The number of TCP connections to the computer that is running SQL Server increases.
•	The Dbo.srch_docspecialprops table in the _Serv database is empty.
•	The following error message is logged in the Uls log files of the server that is configured as the Indexer role: SqlCrawl::ExecuteCommand fails Error 0x80040e2f

If the indexes are not reset, and if a full crawl is started, the following error message is logged in the Uls log files of the server that is configured as the Indexer role:

Back to the top