Have you ever had the above error message? Wondered how to sort it out? Well I am going to show you how.
Today I was confronted by the above error when a test migration database which was setup with replication was loaded with simulated data. The migration source environment was not replicated so I did a check of the length of the data using LEN but thought this was inappropriate as we could solve the problem for a short period before facing the same problem again in testing. I thought it would be best to note the current configuration and then put the max value and note that we would have to change this configuration when it was loaded with data from production.
I checked the current configuration using the below script and screen captured it for future reference.
USE InsertyourDBname;
GO
EXEC sp_configure ‘show advanced options’, 1 ;
RECONFIGURE ;
GO
EXEC sp_configure ‘max text repl size’;
GO
I then decided that I would give the maximum value until later in the project when I could more fine tune this value.
USE InsertyourDBname;
GO
EXEC sp_configure ‘show advanced options’, 1 ;
RECONFIGURE ;
GO
EXEC sp_configure ‘max text repl size’, -1 ;
GO
RECONFIGURE;
GO
This can be done easily via SSMS by going on the server > Properties > Advanced > Max text replication Size > change value to -1.
Originally published at https://parvtheitgeek.com on February 6, 2014.
Parv The IT Geek 