An Issue found in Sitecore User Profile Storage

Recently one of our content authors started to get error like this very frequently.

“Length of LOB data (65608) to be replicated exceeds configured maximum 65536. Use the stored procedure sp_configure to increase the configured maximum value for max text repl size option, which defaults to 65536. A configured value of -1 indicates no limit, other that the limit imposed by the data type….”

sitecore-sql1

It only happens to her account, not others. She manages the site content for about one year. From the error message itself, it is very clear that the SQL Server max text replication need to be increased from default 65536 to a bigger size. I know this SQL server configuration change will fix the issue. But the strange part is what cause the issue and why only happen to specific user account?

I start to trace from SQL Server which Sitecore data is stored. The default setting of “Max Text Replication Size” in SQL Server is 65536 bytes = 0.0625MB. This configuration specifies the maximum size of data that can be added to a replicated column in a single INSERT, UPDATE, WRITETEXT or UPDATETEXT statement. This applies to data type text, ntext, varchar (max), nvarchar (max), varbinary (max), xml, and image.

sitecore-sql3

Sitecore has 3 major SQL databases Core, Master and Web. Since this error happens on Sitecore console application to specific user account, I checked the Core database first to review those table columns with data type in text, ntext, varchar (max), nvarchar (max), varbinary (max), xml, and image. One table come to my eyes, it is “aspnet_Profile”.

By default, Sitecore uses ASP.Net Membership Provider to manage authentication and user profile. These tables are in the Core database. The standard Microsoft implementation of the SqlProfileProvider (which is used in Sitecore by default) stores the user profile information in the “aspnet_Profile” table.

SqlProfileProvider persists profile properties in three fields of the “aspnet_Profile” table: PropertyNames, PropertyValuesString, and PropertyValuesBinary.

The following is a synopsis of what’s stored in each field:  PropertyNames holds a string value containing information about the profile property values present in the PropertyValuesString and PropertyValuesBinary fields. The string holds a colon-delimited list of items; each item denotes one property value, and it is encoded in the following format: Name:B|S:StartPos:Length

PropertyValuesBinary is the field I’d like to check further to determine if it grows wild as it is image data type. Then I use following query to watch the data change in the “aspnet_Profile” table.

SELECT [UserId],[PropertyNames],[PropertyValuesString],
[PropertyValuesBinary],[LastUpdatedDate]  
FROM [dbo].[aspnet_Profile] order by LastUpdatedDate desc

The experiment I did included following activities:

  • Create page and content
  • Modify the page content in content/experience editors
  • Submit page content for approval as workflow required
  • Publish page content

Then I found the size of PropertyValuesBinary only increase when executing workflow command from the Sitecore Content Editor, Experience Editor or Workbox on single item with comment, see below. Every workflow command execution increased about 159 bytes.

sitecore-sql2

Now it explains me the reason why some author accounts started experiencing the error. After websites live for a length of period depends how active authors’ actions in workflow, such as submitting draft, approving, rejecting, etc. Once the data size of PropertyValuesBinary exceeds 65536, the error will show up. I don’t know why Sitecore saves these type of information in “aspnet_Profile” table as binary data, and don’t find how I can reduce or remove them from application console. So here are two solutions: run the following script in Core database to increase the limitation, or create a new account for the author to replace her previous one.

exec sp_configure ‘max text repl size’, 2147483647
Go
RECONFIGURE
GO
This entry was posted in Information Technology, Sitecore and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s