11

I am getting the replication error 'Length of LOB data (2135980) to be replicated exceeds configured maximum 65536'.

sp_configure was previously applied to set it to max value (-1) and a query on sys.configurations confirms that the max value (-1) is in use.

EXEC sp_configure 'max text repl size', -1 ; 
RECONFIGURE; 
GO

select * from sys.configurations
where name like 'max text repl size%';
GO

I have tried using a large value for 'max text repl size' rather than max value and turning it off and on again, to no avail.

Is there something else that may be overriding the 'max text repl size' setting or anywhere else I can look?

  • And you're definitely performing the reconfigure on the correct SQL Server instance? Sorry for the stupid question! – Max Vernon Oct 19 '16 at 18:26
  • 1
    Yes, tried RECONFIGURE, as well as RECONFIGURE WITH OVERRIDE and this is the same instance that the error is occurring on. This is the only instance that is having this issue. No questions are stupid ;-) – Heather Wright Oct 19 '16 at 18:50
  • Not sure if relevant but did you do it on publisher, distributor and subscriber or just publisher? – Vladimir Oselsky Oct 19 '16 at 19:05
  • 1
    Publisher and distributor are on the same instance - this is where the 'max text repl size' setting was applied. – Heather Wright Oct 19 '16 at 19:14
  • have you tried EXEC sys.sp_configure 'max text repl size', 2147483647; ? – Max Vernon Oct 19 '16 at 20:59
8

This configuration can be changed using Management Studio by right-clicking the server -> Properties -> Advanced tab -> Miscellaneous -> Max Text Replication Size. Or you may want to try :

EXEC sys.sp_configure N'max text repl size (B)', N'-1'
GO
RECONFIGURE WITH OVERRIDE
GO
  • 3
    The gotcha with this issue was that the sp_configure also needed to executed on the subscriber as well (it had already been executed elsewhere) because this was where the error was originating. – Heather Wright Jun 14 '17 at 1:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.