A Search SQL Index Issue

Ran into an issue the other day on SharePoint 2010 Search when configuring Managed Properties, specifically I was setting the MaxCharactersInPropertyStoreIndex value on a Managed Property. This is an integer value and is documented here. The maximum value for this property is 450 however if you set this value to its maximum value you will start seeing errors with your crawls and in our case the crawls never would complete. Taking a look at the application event log we can see the following error logged:

Log Name:      Application
Source:        Microsoft-SharePoint Products-SharePoint Server Search
Date:          6/26/2011 7:24:02 AM
Event ID:      57
Task Category: Search service
Level:         Warning
Computer:      COMPUTERNAME
A database error occurred. Source: Microsoft SQL Server Native Client 10.0 Code: 1946 occurred 4097 time(s) Description: Operation failed. The index entry of length 904 bytes for the index ‘IX_Str’ exceeds the maximum length of 900 bytes.


Taking a look at the MSSDocProps table I found the IX_Str index is defined as so:

CREATE_INDEX(MSSDocProps, IX_Str)([Pid], [strVal]) ON [PRIMARY]

The issue here is that 450 characters is actually 900 bytes because we store these characters in Unicode which is actually 2 bytes per character. Add the 4 bytes for the PID and we have 904 bytes for the index which is over the 900 byte max SQL has for index values. To work around the issue I to set the MaxCharactersInPropertyStoreIndex to 450-2 to account for the 4 bytes for the PID and this corrected the issue.

So the real maximum value for MaxCharactersInPropertyStoreIndex should be 448.

Hopefully you will never run into this issue but if you customize Search you should be aware of this limit.

One thought on “A Search SQL Index Issue

Comments are closed.