Knowledge

[Sonja_Bauernfeind]

Environment:

Qlik Replicate 

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 image data 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. 

Environment:

Qlik Replicate 
SQL Server

Explanation

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.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

   at System.Data.SqlClient.SqlDataReader.get_MetaData()

   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)

   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

   at System.Data.SqlClient.SqlCommand.ExecuteScalar()

   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

 

Resolution

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 allowing values 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:

1. Identify if there are any replication/CDC configured with the table/database
2. Identify the LOB datatype column(s) in the affected table(s)
3. 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.
4. 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)”
5. Set the required or expected size for ‘max text repl size (B)’ configuration if possible, instead of the generic maximum -1

CLI

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 

GUI

1. Right-click on Server Level and go to Properties
2. Open Advanced in the menu to the left
3. Locate Max text Replication Size in the Miscellaneous section
4. Enter your desired value.