One of the advanced Server level options for SQL Server is Max Text Replication Size and this is really not a common one. In most of cases the default value is not changed.

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.

The default value for this configuration is 65536 bytes = 0.0625 MB.

Why we care about this value? Here is a reason why we might have to carefully change this value according to our requirements.

Recently I had setup a database for Citrix XenApp 6.5 farm data store requirements and configured transactional replication as part of the DR requirement.

After configuring replication the Citrix team where able to read data out of the database, however nothing was getting published. They were consistently getting errors like “Unknown error occurred: error code 0×82060035″

Things where working great before and this error started popping up after replication was setup. The only change made was setting replication for this database.

Upon checking the database closely I found out one table called dbo.KEYTABLE which had a column called data which was of type Varbinary (max).

As this column was part of transactional replication the value of Max Text Replication Size came into picture and anything above 65536 bytes on a single Insert was not allowed and Citrix faced issues while publishing new apps.

Carefully choosing the best possible value for this configuration setting fixed the problem.

It has a max value of 2147483647 bytes which is 2 GB.

I really don’t recommend directly increasing the value to 2147483647 bytes, and I would always test a good value which works fine for the environment.

High value will allow huge Inserts,Update in a single statement and can bring in network latency while replicating.

I had a twitter discussion with SQLServer expert Robert L Davis(B/T) to double check if changing this server level value has any other direct impact, and he confirmed that there aren’t any. Many thanks to Robert!

Thanks for reading.