Office Server Search memory leak and stuck on "crawling"

It is the typical scenario isn’t it. Site works fine for a week and then is officially launched on a Monday morning and the site breaks after an hour complaining that it cannot connect to the configuration database. Whoa?

The SQL Server was checked and confirmed to be running fine, and in checking the SharePoint web front end server I I noticed was MSSEARCH.EXE was chewing memory at a rapid rate of knots. Checking the event logs showed up a steady sequence of event ID 7888, 10036 and 3355 messages. Later I noticed that the search crawl was stuck on "Crawling".

If you search on this topic, many people recommend recreating your Shared Service Provider. In this case, this is unnecessary (not to mention drastic).

It turned out to be an unfortunate combination of factors.

• The client was using SQL Server 2005 with SP1
• The client had a SQL Server maintenance plan with a "rebuild index" task.

Non SQL reader? Maintenance plans are a "good thing". Think of it as a fitness regime for your SQL Server. You can regularly perform integrity checks, tasks to optimise performance, run backups and the like. A screenshot of a basic SQL2005 maintenance plan is to the right.

As it turns out, my client ran a maintenance plan each Sunday (hence why this broke on the Monday of go-live). It also turns out that the "rebuild index" maintenance plan task in SQL Server prior to SP2 has a teeny weenie problem. (By "rebuilding" an index, we mean that it is in effect, deleting and recreating it again).

When rebuilt, various options set on the indexes are not recreated the same way as they were originally created. As you may have guessed, this is extremely uncool, since SharePoint set various indexes in a certain way, it expects things to remain consistent.

There is a KB that you can read all about it here.

http://support.microsoft.com/kb/930887/en-us

Also, another great blog post about the issue is here:

http://blogs.vertigo.com/personal/michael/Blog/Lists/Posts/Post.aspx?ID=4

Some of effects of this dodgily recreated index are listed in the KB article, including:

1. In Shared Services, the "indexing status" remains stuck in the "Crawling" state
2. The number of handles that are opened by the MSSearch.exe process increases
3. The number of TCP connections to the computer that is running SQL Server increases
4. Several error are recorded in the SharePoint logfiles.

• "SqlCrawl::ExecuteCommand fails Error 0x80040e2f"
• CGathererQueueManager::FlushQueue failed with recoverable error 0x80040e2f
• CGathererFilterSink::CommitLinks : pGatherAddLink->AddLinkComplete error=0x80040e2f

Additionally, in my case, MSSEARCH.EXE leaked memory very rapidly when viewed in Task Manager and I received the event logs as shown at the end of this post.

To resolve it, follow the instructions at Michael’s blog as the KB article is poorly written, but make sure that you do it for all tables as listed in the KB article! Michael’s post only covers 2 indexes and there actually are more in the search database as shown below.

• Event Type:      Error
• Event Source:   Office SharePoint Server
• Event Category:            Office Server General
• Event ID:          7888
• Date:                1/07/2008
• Time:                12:03:03 PM
• User:                N/A
• Computer:         MyServer

Description:

Message: A connection was successfully established with the server, but then an error occurred during the pre-login handshake.  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: 0 – No process is on the other end of the pipe.)

• Event Type:      Error
• Event Source:   Office Server Search
• Event Category:            Gatherer
• Event ID:          10036
• Date:                1/07/2008
• Time:                12:03:38 PM
• User:                N/A
• Computer:         MyServer

Description:

A database error occurred.

Source: Microsoft OLE DB Provider for SQL Server

Code: 17 occurred 1 time(s)

Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

• Event Type:      Error
• Event Source:   Windows SharePoint Services 3
• Event Category:            Database
• Event ID:          3355
• Date:                1/07/2008
• Time:                12:04:08 PM
• User:                N/A
• Computer:         MyServer

Description:

Cannot connect to SQL Server. MyServer not found. Additional error information from SQL Server is included below.

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

• Event Type:      Error
• Event Source:   Office SharePoint Server
• Event Category:            Office Server Shared Services
• Event ID:          6482
• Date:                1/07/2008
• Time:                12:05:12 PM
• User:                N/A
• Computer:         MyServer

Description:

Application Server Administration job failed for service instance Microsoft.Office.Server.Search.Administration.SearchServiceInstance (009e9001-5c8f-4705-9b4d-ee514c442fc7).

Reason: Exception from HRESULT: 0x80040D1B

Techinal Support Details:

System.Runtime.InteropServices.COMException (0x80040D1B): Exception from HRESULT: 0x80040D1B

   at Microsoft.Office.Server.Search.Administration.SearchServiceInstance.SynchronizeDefaultContentSource(IDictionary applications)

   at Microsoft.Office.Server.Search.Administration.SearchServiceInstance.Synchronize()

   at Microsoft.Office.Server.Administration.ApplicationServerJob.ProvisionLocalSharedServiceInstances(Boolean isAdministrationServiceJob)

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

07/01/2008 12:25:01.85 mssearch.exe (0x0E20)                          0x0E38 Search Server Common             GathererSql                               0          Monitorable       CSqlCrawl::ExecuteCommand fails Error 0x80040e2f – File:d:\office\source\search\search\gather\server\gathersql.cxx Line:407