Monday, March 5, 2018

java.sql.SQLException: Length of LOB data () to be replicated exceeds configured maximum 65536


We recently come across below database error when working with an application.

java.sql.SQLException: Length of LOB data (65594) 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.

The maximum value for max text repl size defaults to 65536. We get this error when a truncation data size for any of the replicated text column exceeds the limit.

To check the current max text replication size run below command.

USE <DB Name>;
GO
EXEC sp_configure 'show advanced options', 1 ;
RECONFIGURE ;
GO
EXEC sp_configure 'max text repl size';
GO



Output:-
name                              minimum           maximum    config_value      run_value
max text repl size (B)     -1                      2147483647    65536                   65536

You can set the value to maximum by running below command.
EXEC sp_configure 'show advanced options', 1 ;
RECONFIGURE ;
GO
EXEC sp_configure 'max text repl size', -1 ;
GO
RECONFIGURE;
GO


Note that -1 indicates that there is no limit set for 'max text repl size' other than imposed by the data type.

You can do the same using SSMS
  1. Open SSMS and connect to Server/instance
  2. Right-click on the server/instance name and choose properties
  3. Select “Advanced” options on the properties page.
  4. Under  “Miscellaneous” header  you will see the current value of  
    “Max text replication Size”.
  5. Change the default value from 65536 to -1 or 2147483647 (depending on the SQLserver) and press OK.
 Regards
Satishbabu Gunukula

19 comments:

  1. I am only writing to make you be aware of what a perfect experience my friend's girl developed browsing the blog. She came to understand lots of details, most notably what it is like to have a wonderful helping mindset to make men and women quite simply completely grasp specified impossible subject matter. You really did more than our expected results. Thanks for rendering these priceless, dependable, explanatory as well as cool guidance on that topic to Emily. Singapore web development firms

    ReplyDelete
    Replies
    1. Great Article android based projects

      Java Training in Chennai Project Center in Chennai Java Training in Chennai projects for cse The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

      Delete
  2. Attend The PMP Certification in Abu Dhabi From ExcelR. Practical PMP Certification in Abu Dhabi Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The PMP Certification in Abu Dhabi.
    ExcelR PMP Certification in Abu Dhabi

    ReplyDelete
  3. Hi are using Wordpress for your blog platform? I'm new to the blog world but I'm trying to get started and create my own. Do you require any html coding knowledge to make your own blog? Any help would be greatly appreciated! List of Agriculture Companies in Singapore

    ReplyDelete
  4. Wonderful blog post. This is absolute magic from you! I have never seen a more wonderful post than this one. You've really made my day today with this. I hope you keep this up!
    data science course

    ReplyDelete
  5. I am impressed by the information that you have on this blog. It shows how well you understand this subject.
    data analytics course
    big data analytics malaysia
    big data course

    ReplyDelete
  6. Hi, i think that i saw you visited my website so i came to “return the favor”.I'm trying to find things to improve my site!I suppose its ok to use some of your ideas!! livestream singapore

    ReplyDelete
  7. Simply wish to say your article is as surprising. The clarity in your post is just great and i can suppose you are a professional on this subject. Fine along with your permission let me to seize your feed to stay updated with approaching post. Thank you a million and please keep up the enjoyable work.
    supply chain management course singapore

    ReplyDelete
  8. I am not sure where you're getting your information, but great topic. I needs to spend some time learning much more or understanding more. Thanks for great info I was looking for this info for my mission. everyday gold necklace

    ReplyDelete
  9. Attractive section of content. I just stumbled upon your web site and in accession capital to assert that I get actually enjoyed account your blog posts. Anyway I’ll be subscribing to your feeds and even I achievement you access consistently quickly. Loyalty programs Singapore

    ReplyDelete
  10. I cling on to listening to the news speak about getting free online grant applications so I have been looking around for the best site to get one. Could you tell me please, where could i find some?solidworks training singapore

    ReplyDelete
  11. You can certainly see your expertise in the work you write. The world hopes for even more passionate writers like you who aren't afraid to say how they believe. Always go after your heart. business growth

    ReplyDelete
  12. Hello just wanted to give you a brief heads up and let you know a few of the pictures aren't loading correctly. I'm not sure why but I think its a linking issue. I've tried it in two different browsers and both show the same results. buy paintings online

    ReplyDelete
  13. I'm typically to blogging and i really respect your content. The article has actually peaks my interest. I am going to bookmark your site and keep checking for brand spanking new information. pest control company in Singapore

    ReplyDelete
  14. Great post. I was checking continuously this blog and I'm impressed! Extremely useful information specially the last part :) I care for such info much. I was looking for this certain information for a long time. Thank you and good luck. clutch kit

    ReplyDelete
  15. Thank you, I have recently been searching for information about this topic for ages and yours is the greatest I've discovered till now. But, what about the conclusion? Are you sure about the source? energy shot supplement

    ReplyDelete
  16. I will right away grab your rss as I can't find your e-mail subscription link or newsletter service. Do you've any? Please let me know in order that I could subscribe. Thanks.residential cleaning services

    ReplyDelete