SQL Server 2005 Maintenance Plans and MOSS 2007 Databases

  • Article

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:

sps3://domain.company.com
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'

Details:

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.