Article ID: 930887
Article Last Modified on 10/15/2007
APPLIES TO
- Microsoft Office SharePoint Server 2007
- Microsoft Office SharePoint Portal Server 2003
SYMPTOMS
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.
CAUSE
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.
RESOLUTION
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.
| 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 |
| 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.
MORE INFORMATION
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:
- 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: This error message occurs when data is inserted into the Srch_docspecialprops table.
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:
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:
Keywords: kbtshoot kbprb kbexpertiseinter KB930887
