MOSS Database Connectivity / Crawler Problem

Recently we ran into a strange problem where after a variable length of time, sometimes hours, sometimes days, our MOSS application server would lose connection with our content database.  The event viewer would be inundated with messages similar to this:

Cannot connect to SQL Server. <servername> not found. Additional error information from SQL Server is included below.
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

and

A runtime exception was detected. Details follow.
Message: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

We were fairly sure this didn’t have anything to do with remote connections, they had been setup fine since the beginning, and rebooting the application server always seemed to fix the problem temporarily.

The other problem that we seemed to be having was that crawls of our site seemed to be getting “stuck” in the “Full Crawl” state within SharedServices.  Attempting to stop the crawl via the gui manager and through stsadm -o spsearch -action fullcrawlstop were unsuccessful.  The most that was accomplished was that a message was placed in the log saying that there was an “attempt” to stop the crawl, but it clearly wasn’t working properly.

Both of these were pretty important issues, when I ran across a solution that effectively killed both of these birds with one stone here:

http://www.sharepointblogs.com/gnarus/archive/2007/02/12/the-indexing-status-remains-in-the-crawling-state.aspx

The author concludes that this was happening because the Database server that SharePoint 2007 was using was a SQL 2005 server which has maintenance plans that rebuilds indexes. This issue was occurring because the Rebuild index task did not restore all the options that were set on the indexes that are used by SharePoint Server 2007 after indexes are rebuilt. The Ignore duplicate values option is turned off after indexes are rebuilt.

I would suggest heading there to see the full problem and resolution, but here is at least the resolution part repeated in case that site goes down or something.  This worked for us, but the maintenance plan kept bringing us back to the same problem.  We later discovered that upgrading to SQL Server 2005 SP2 helped with the maintenance plan problem (we were using SP1).  The knowledge base entry that was the source of this is found here: http://support.microsoft.com/kb/930887

Here is the RESOLUTION
To resolve this issue, you need to 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.
 
Go into your SQL management studio and change the following tables in your search database to have the Ignore duplicate values check box enabled

* (You may have to temporarily click The Unique check box on the General tab on the Index Properties page so that the Ignor Duplicate check box is not grayed out)
 
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.

Advertisements

0 Responses to “MOSS Database Connectivity / Crawler Problem”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s





%d bloggers like this: