Microsoft KB Archive/930887

= You experience issues with portal alerts in SharePoint Portal Server 2003 or with search performance in SharePoint Server 2007 after you create SQL Server 2005 maintenance plans =

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 &quot;More Information&quot; 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.

SharePoint Server 2007
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 &quot;Ignore Duplicate Values&quot; 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.



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.</li> A SQL profiler trace that is taken during a crawl contains the following SQL Server 2601 error message:

&quot;Cannot insert duplicate key row in object&quot;

This error message occurs when data is inserted into the Srch_docspecialprops table.</li></ul>

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.</li> The number of TCP connections to the computer that is running SQL Server increases.</li> The Dbo.srch_docspecialprops table in the _Serv database is empty.</li> 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

</li></ul>

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:

CGathererQueueManager::FlushQueue failed with recoverable error 0x80040e2f CGathererFilterSink::CommitLinks : pGatherAddLink->AddLinkComplete error=0x80040e2f

Keywords: kbtshoot kbprb kbexpertiseinter KB930887

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.