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 translations Article translations
Article ID: 930887 - View products that this article applies to.
Expand all | Collapse all

On This Page

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.

SharePoint Portal Server 2003

Collapse this tableExpand this table
DatabaseTable nameIndex name
_ServSrch_docspecialpropsIX_CatIdDocId
_ServSub_PropVersionsIX_srch_propVersions_non_Clustered
_ServSupportedPropsIX_Unique_Uri
_ServURIHashIX_URIs
_ProfProfileImport_copyIX_ProfileImport_copy

SharePoint Server 2007

Collapse this tableExpand this table
DatabaseTable nameIndex name
SearchMSSAlertDocHistoryIX_AlertDocHistory
SearchMSSAnchorChangeLogIX_MSSAnchorChangeLog
SearchMSSAnchorPendingChangeLogIX_MSSAnchorPendingChangeLog
SearchMSSCrawlChangedSourceDocsIX_MSSCrawlChangedSourceDocs
SearchMSSCrawlChangedTargetDocsIX_MSSCrawlChangedTargetDocs
SearchMSSCrawledPropSamplesIX_MSSCrawledPropSamplesByDocid
SearchMSSCrawlErrorListIX_MSSCrawlErrorList_hrResult
SearchMSSCrawlHostListIX_MSSCrawlHostList_Name
SearchMSSCrawlQueueIX_MSSCrawlQueue
SearchMSSDocSdidsIX_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

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.

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:
CGathererQueueManager::FlushQueue failed with recoverable error 0x80040e2f CGathererFilterSink::CommitLinks : pGatherAddLink->AddLinkComplete error=0x80040e2f

Properties

Article ID: 930887 - Last Review: June 21, 2014 - Revision: 4.0
Applies to
  • Microsoft Office SharePoint Server 2007
  • Microsoft Office SharePoint Portal Server 2003
Keywords: 
kbexpertiseinter kbtshoot kbprb KB930887

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com