SQLServerTimes2

This is a quickie blog post, but I thought I’d post it before I forgot about it.

 

We have transactional replication turned on for one of our production databases.  This database includes XML fields and we recently saw the following error:

 

           Length of LOB data (78862) to be replicated exceeds configured maximum 65536

 

Looking into it, this is a server setting and the default value for Max Text Replication Size is 65536 (bytes).   Because our XML data is of varying sizes, I made the decision to set this to the maximum allowable value – 2147483647 (bytes). 

 

To change it through SSMS:

•                Right click on the server and choose Properties
•                In the Advanced page – change the Max Text Replication Size to 2147483647

In T-SQL:

EXEC sp_configure ‘max text repl size’, 2147483647

GO

 

 

 

2 Responses so far | Have Your Say!

1. 

   Kendal Van Dyke
   December 2nd, 2009 at 8:22 am #

   Be careful about setting the value to the max (2147483647)…at this setting you’re allowing up to 2 GB to be replicated in a single transaction. That’s 2 GB that needs to be stored in the distribution DB and delivered over the wire in a transaction.

   I usually ask my developers to pick a more realistic max size of a few MB to keep them from shooting themselves in the foot.

2. 

   tledwards
   December 2nd, 2009 at 8:27 am #

   I agree – in theory. In practice, though, I haven’t been able to get them to give me a more realistic size and each time I had set it at a smaller size, we experienced failures. Fortunately, our throughput is pretty good and we haven’t had any issues so far. Thanks for the input, though.