SQL Server 2008 -> Replication Related Error

Cristóvão Honorato

Question

Symptoms:

When inserting a record into a replicated table, the following SQLServer error message can appear:
"Length of text, ntext, or image data (x) to be replicated exceeds configured maximum 65536"

Example error stack:

Message :Length of text, ntext, or image data (96491) to be replicated exceeds configured maximum 65536.
The statement has been terminated.

Stack: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
(...)

The insert is unsucessfull.

Cause:

Replicating large amounts of data will introduce latency. To prevent this, SQL server caps how much data can be replicated in a single column using the Max Text Replication Size setting. This setting has default value of 65536. If you try to replicate text data which exceeds this length, you will get the error message stated above.

Resolution:

You can work around this situation by changing the setting 'max text repl size'. You can do it, using a SQLServer stored procedure called 'sp_configure'.

Example Fix:
EXEC sp_configure 'max text repl size', @NewSize
RECONFIGURE WITH OVERRIDE

Links:

https://www.novicksoftware.com/TipsAndTricks/tip-sql-server-image-replication-maximum-size-configured.htm
https://msdn.microsoft.com/en-us/library/aa259616.aspx

18 Jun 2009

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines