Search failing periodically -- Event ID 10027

Question

WSS 3.0 SP2 -- Every so often our search indexer will stop scanning and a bunch of these errors will appear in our event log

Failed to update committed transaction in SQL, DocID is 347155.
Context: Application 'Search index file on the search server', Catalog 'Search'
Details:   Value violated the integrity constraints for a column or table.   (0x80040e2f)

The error will occur every minute or 2.  What I have found is that the doc ID in question is tied to a document list or library that has been deleted out of SP.  There seem to be few options on getting it to work again short of recreating the entire search DB (stopping the service through CA).  I don't like to do that as we lose all searching for several hours.  What I can do is open the search DB itself (yeahyeah I know the problems with that) and delete any reference to this DocID in the MSSCrawlURL and MSSCrawlQueue tables.  Repeat for any of the docIDs that show up.  Usually after deletion a new one pops up.  This happens for 10-15 more then it goes away.  Sometimes it will cause the indexer to wig out after that, sometimes it won't.  Most times after that I will run stsadm -o spsearch -action fullcrawlstop and fullcrawlstart and that will fix the latter issue (it never fixes the former though).

2 questions pertaining to this -- Why would it be happening, and is there a way to stop it?

Thanks
Chris

Answer 1

Hello Chris,
Please make sure you are running at least SQL 2005 SP2 or SQL 2000 SP4 on your SharePoint database instance. Here is knowledge base article that may help to resolve this error.
http://support.microsoft.com/kb/930887

---

Chad Schultz -MSFT

Answer 2

Hi Chris,

 

You can try to reset the crawled content by following the steps below:

1.     Open “Central Administration” on the SharePoint Server and click the Shared Service Provider's name on the left hand side.

2.     Click "Search settings"à"Reset all crawled content"

3.     Select the “Reset Now” button on the “Reset Crawled Content” page.
That should result in the "Items in index" to be set to 0.The next step is to perform a full crawl on your content source(s).

4.     Open “Central Administration” and click the Shared Service Provider's name on the left hand side.

5.     Click "Search settings"à"Content sources and crawl schedules" link.

6.     Select the content source you wish to crawl.

7.     At the bottom of the content source's page there is a checkbox with the words "Start full crawl of this content source". Check this box and select OK.

 

Hope this helps.

 

Lu Zou

Answer 3

Thanks for the idea.  We are only running SP1 on SQL 2005, so I will see if it is possible for us to upgrade.  It is a shared SQL box though so it might not be an option.  We have no maintenance plans in place on our SQL server for the search DBs, but I have changed all the indexes to Ignore Duplicate Values, so I will see what happens there.

Answer 4

Thanks for the post.  We are only using WSS so there is no Share Service Provider for search.

--Chris

Answer 5

It looks like changing the index to ignore duplicates has fixed the issue for now.