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

2 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
  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