After having had the MS-Replication\CDC enabled on a stand-alone or AG enabled server, customer applications start getting the following errors from the database engine, when attempting any DML involving long text or LOB fields:
“Length of LOB data 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. A configured value of -1 indicates no limit, other that the limit imposed by the data type. The statement has been terminated”
This error means that the length of a text, ntext, varchar(max), nvarchar(max), varbinary(max), xml, or image data field participating in INSERT\UPDATE or INSERTTEXT\UPDATETEXT DML is exceeding the configured value of 65536 (a SQL server default).
There is a configuration “max text repl size (b)” in SQL Server which can be viewed by sp_configure system procedure or querying sys.configurations system table. This parameter has an impact on systems configured with replication and\or Change Data Capture.
From the above picture and the description of ‘max text repl size (B)’ configuration it becomes clear that the value of the parameter denotes the maximum length in bytes of a text field in replication. However, we need to understand the threshold is also applicable to other datatypes such as Text, Ntext, Varchar(max), Nvarchar(max), Varbinary(max),XML, and imagedata types as well. SQL Server will return this error each time the length of the data in the DML is greater than the established threshold. The DML operation gets terminated for the same reason.
When replication is enabled from Qlik Replicate with admin access to the DB, all the artifacts necessary for the replication will get created (articles, publications, etc…). Once this is done, additional data will start getting written into the database’s transactional log (TLOG) to allow the SQL Server log-based replication. Together with limited size data types, extended datatypes (like LOBs [Large Objects]) will need to be written to the log, which could be a costly endeavor for the database. To prevent any negative impact of long text or LOBs being written to the TLOG, SQL server by default limits the size of “replicateable” LOBs with the “Max Text Repl Size (B)” database parameter (see the image below). This can affect the applications, as any DML containing a field longer than the maximum threshold defined by “max text replicate size” parameter will be terminated by SQL server with a message “Length of LOB data (affected value length) to be replicated exceeds configured maximum (configured length)”.
As a result, any application that attempts to modify any LOB value in the database may fail with an error like the one shown below:
2022-04-06 08:46:26.267994;Information;GILM.Domain.Scheduling.TaskBase.ExecuteExportAndMaintenanceTasks: Length of LOB data (15124640) 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. A configured value of -1 indicates no limit, other that the limit imposed by the data type.
The statement has been terminated.
2022-04-06 08:46:26.283620;Information;GILM.Domain.Scheduling.TaskBase.ExecuteExportAndMaintenanceTasks: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteScalar(IDbCommand command)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteScalar(DbCommand command)
at GILM.Utilities.Common.DatabaseAdapter.ExecuteScalar(SQLStatement sqlStatement, String connectionStringName) in D:\TB2\56\s\GILM.Utilities.Common\Persistence\DataAccess\DatabaseAdapter.cs:line 22
To allow a database process large object datatypes while the database is enabled for replication, it will need to be configured consistently to allow longer LOBs in replication. A simple solution would be to set the configuration 'max text repl size(b)' to a value of -1 allowingvalues of any supported size.
However, this may have some detrimental impact on replication as the size increases, we need to make sure the replication has no other side effects due to the size of the data to be replicated over the network etc.
To be less generic, a following approach is recommended:
Identify if there are any replication/CDC configured with the table/database
Identify the LOB datatype column(s) in the affected table(s)
Assess your data and conclude the maximum length of data it can reach up to as per the business requirement. Note that special handling may be required for multi-byte encodings. As an example, a photo field can be restricted with a specified size through application that can be uploaded etc.
Assess the max size or length of the data in the table and confirm that the size of the data is exceeding the configured value of “max text repl size (B)”
Set the required or expected size for ‘max text repl size (B)’ configuration if possible, instead of the generic maximum -1
USE DBNAME; GO EXEC sp_configure 'show advanced options',1 RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure 'max text repl size (B)', -1 RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure 'show advanced options',0 RECONFIGURE WITH OVERRIDE; GO
Right-click on Server Level and go to Properties
Open Advanced in the menu to the left
Locate Max text Replication Size in the Miscellaneous section