SQL Server 2005 Maintenance Plans and MOSS 2007 Databases

Earlier I saw one crawling error in my customer's new MOSS environment after upgraded from SPS 2003. It's not necessary related to the upgrade though.


I saw the following error in the crawl log: 

Exception from HRESULT: 0x80040E2F (Exception from HRESULT: 0x80040E2F)

From the event viewer on the index server:

The start address <sps3://domain.company.com> cannot be crawled.

Context: Application 'sharedservices1', Catalog 'Portal_Content'


Value violated the integrity constraints for a column or table. (0x80040e2f)

I just found out there’s potential issue when there’s a SQL maintenance plan in place that rebuilds the indexes including SharePoint 2007 databases. The error we saw earlier about integrity violation (0x80040e2f) may occur after the SQL maintenance plan changes the index properties that SharePoint expects to be in place.

I would suggest that you change your SQL maintenance plans to exclude the SharePoint databases from receiving an Index rebuild. At least until more guidance is available from Microsoft on how to use SQL Maintenance and SharePoint 2007. 


Our product team have reviewed the SQL maintenance tasks and have made the following recommendations.

Tasks                                                    Considered Safe

- Check DB                                         Yes

- Shrink DB                                         Yes

- Reorganize Indexes                         Yes

- Cleanup History                               Yes

- Update Statistics                              Yes but unnecessary since timer service does this automatically.

- Rebuild Indexes                               No, if the task that ships with SQL 2005 management studio is used. Using scripts that restore all options is allowed.


Although this known issue seems to the cause of my customer's crawl error but before we tried contacting the Premier support for a fix, we worked around the issue and now the crawling and people searching are working just fine :-).


The trick we did was:

Delete the existing People scope from Search settings, re-create a new People scope using the exact same settings as before. Restart the full crawl. The People scope should display a non-zero number of items. When it failed, it displayed 0 items in the View Scopes area.


The maintenance plan issue is still a good thing to know for future reference and I would try to pay attention to additional advice from Microsoft for this.




Comments (2)
  1. For my future reference (and yours as well), here is how to configure your SQL Server 2005 maintenance

Comments are closed.

Skip to main content