when insert length of lob data to be replicated exceeds configured maximum 65536

Question

I am trying to insert a value into a column of datatype image in SQL Server. I am getting the following error:

Length of LOB data (70823) to be replicated exceeds configured maximum 65536.  
The statement has been terminated.

The data length is less than 2 MB.

What is the problem?

I am getting this error in asp.net application. – gopal May 29 '09 at 12:57 — gopal, May 29 '09 at 12:57
you should mark the answer that has helped you as correct – Gabriel Guimarães May 12 '11 at 18:51 — Gabriel Guimarães, May 12 '11 at 18:51

Garry Shutler · Answer 1 · 2012-10-23T12:24:45.137

19

For SQL Server 2005 or earlier you can run:

sp_configure 'max text repl size', 2147483647

For SQL Server 2008 or later you can run:

sp_configure 'max text repl size', -1

The former increases the maximum size allowed, the latter essentially says "remove the limit". Once the maximum size is increased or removed, large LOBs will be able to be replicated.

edited Oct 23 '12 at 12:24

answered May 29 '09 at 13:06

Garry Shutler

32,260
12
84
119

Do you happen to know how to fix the same problem when it occurs for columns of type VARBINARY(MAX)? (They're also FILESTREAM if that matters). – David Moye Oct 14 '10 at 16:01
1

How come you do not recommend setting the size option to -1 or no maximum? – Nat Oct 15 '10 at 01:42
@Nat it looks like -1 only became available as of SQL 2008 (see Brian's comment here: http://sqlmusings.wordpress.com/2008/09/22/issue-length-of-lob-data-to-be-replicated-exceeds-configured-maximum-65536/) – JohnLBevan Oct 19 '12 at 22:15
Amended my answer to add the -1 case. – Garry Shutler Oct 23 '12 at 12:26
What if you don't want to change the size though, how do you find the table and column causing the issue? – Tom Oct 03 '14 at 08:33

score 5 · Answer 2 · answered Aug 07 '10 at 16:52

5

Don't forget to run RECONFIGURE after running sp_configure so that your changes can take effect

answered Aug 07 '10 at 16:52

ram

11,468
16
63
89

score 1 · Answer 3 · answered Dec 09 '13 at 23:41

As an alternative, you can configure the max text replication size option via SQL Server Management Studio

Reference: http://msdn.microsoft.com/en-us/library/ms179573.aspx

To configure the max text repl size option
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.

I've tested it in SQL Server 2012, but from the previous link you can view the instructions for 2005 and 2008 which are the same.

when insert length of lob data to be replicated exceeds configured maximum 65536

3 Answers3

Linked