Hello everyone. In today’s post we are going to see how to increase the value of max text repl size in SQL Server. This value is specific to replication in SQL Server. You have more details about replication in this entry .
The option max text repl size specifies the maximum size (in bytes) of type data text , ntext , varchar (max) , nvarchar (max) , varbinary (max) , xml and image that can be added to a captured or replicated column in a single INSERT, UPDATE, WRITETEXT, or UPDATETEXT statement. The default is 65,536 bytes. A default value of -1 indicates that there is no size limit, except that imposed by the data type.
Although it can be set without limit with the value -1, it is not recommended, as it can lead to timeout problems.
When is it necessary to increase the value of max text repl size?
When we encounter an error of the following type, it is necessary to change the value of max text repl size so that it allows the field to be replicated, in this case of the LOB type. This type of field has a maximum size of 2 GB, so we will set this value, thus avoiding using -1.
This error occurred recently on a client with NAVISION. If we look at the message, it tells us that the field in question has a length of 66323 and the limit is set to 65536 so it is not possible to replicate the change by a small margin.
To correct this error, we set the limit to 2 GB using T-SQL as follows:
USE master; GO EXEC sp_configure 'show advanced options', 1; RECONFIGURE; GO EXEC sp_configure 'max text repl size', 2147483647; GO RECONFIGURE; GO
After applying this change, this error did not occur again.
If you have problems with replication in SQL Server or want us to assess your options for high availability such as replication or AlwaysOn, do not hesitate to contact us.
Don’t miss out on any more tricks like this in future posts. You can receive them all in one newsletter monthly by subscribing to it. With a single email a month you will be informed.
Follow GPS on LinkedIn
See you in next innings.