belle’s sql musings

Issue: Length of LOB data to be replicated exceeds configured maximum 65536

Posted in Issues/Troubleshooting by belle on September 22, 2008

Error:

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

Scenario:

We published some articles that use varchar(max) and a lot of XML data types for the columns. When we enabled replication, we got the error Length of LOB data (78862) to be replicated exceeds configured maximum 65536

Solution:

Increase the size that can be replicated. This is applicable for transactional replication only.

T-SQL: 
EXEC sp_configure ‘max text repl size’, 2147483647

SSMS (excerpt from BOL):

    1. In Object Explorer, right-click a server and select Properties.
    2. Click the Advanced node.
    3. Under Miscellaneous, change the Max Text Replication Size option to the desired value.

Reference:

BOL ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3056cf64-621d-4996-9162-3913f6bc6d5b.htm

Tagged with:

13 comments

13 Responses

Subscribe to comments with RSS.

  1. glompix said, on June 4, 2009 at 9:37 am

    Great post. Exactly what I was looking for. Nice and simple. 😉

    Reply
  2. Eric said, on June 24, 2010 at 8:52 am

    Excellent post. Helped me identify a solution very quickly!

    Reply
  3. Dave said, on November 9, 2010 at 3:42 pm

    Thanks for posting. Perfectly fixed our problem 🙂

    Reply
  4. Alex said, on March 21, 2011 at 2:58 pm

    Thank you guys.

    Reply
  5. Brian Jaramillo said, on April 19, 2011 at 3:29 pm

    Perfect! Thanks for posting.

    *I know this post is a couple of years old, so those of you viewing for the first time should know that this is a good fix for SQL 2005.

    For SQL 2008, you can use also use -1 to remove the limit from the field.

    T-SQL:

    EXEC sp_configure ‘max text repl size’, -1

    RECONFIGURE

    Reply
  6. Thom Bolin said, on June 20, 2011 at 10:58 am

    Thanks for the simplicity of the post, not a lot of fluff just the facts

    Reply
  7. Sanjeev said, on October 4, 2011 at 2:58 am

    Nice Job, this help me a lot..!! thanx

    Reply
  8. andres said, on November 18, 2011 at 1:22 pm

    Excellent!!! Nice and simply!! Solved my prob!!!

    Reply
  9. Bradley Ball (@SQLBalls) said, on March 19, 2012 at 7:50 am

    Thanks for the post, exactaly what I was looking for!

    Reply
  10. Mark said, on October 1, 2012 at 9:02 pm

    Doesn’t fix the problem!!!

    Reply
  12. vinod said, on December 11, 2012 at 2:20 pm

    awesome post. perfect thing

    Reply
  13. Declan said, on September 9, 2013 at 6:35 am

    excellent thanks for the help

    Reply

Leave a reply to Alex Cancel reply