Microsoft SQL Server 2005 RTM merge replication issues

We are trying to implement merge replication between 2 servers. Database size is around 11GB.

When I tried to create publication, snaphot agent is failing with following errror.

Error: 14151, Severity: 18, State: 1.
Replication-Replication Snapshot Subsystem: agent NBTENTSQL1X-PayDirectWeb-PayDirectPub-2 failed. The
replication agent had encountered an exception.
Source: Replication
Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
Exception Message: String or binary data would be truncated.
Cannot find the object 'MSmerge_sel_4D388

The table where the snapshot is breaking contains around 169 columns. Can someone please help me get through this error ?

Are you using vertical partitioning to replicate subset of the columns of the table? You may be hitting an known issue in Microsoft SQL Server 2005 where the list of column names for the table is longer than 4K and hence gets truncated in an internal proc.

|||

Thanks Sudarshan, your reply is very helpful. No we are not using vertical partitioning.

If this is a known issue, did Microsoft have any immediate fix for it or we have wait for SQL2K5 SP2 ?

|||Please wait for SQL2K5 SP2. You can install the latest CTPs availalbe and test out that your scenarios works. If not, please reply back and we can take a look further.|||This is a known issue in SQL2K5 SP1 which is fixed in SP2.|||Does the same limitation apply to the complexity of joined tables in the filter? I'm getting an error in the snapshot agent, saying:

String or

binary data would be truncated.

Cannot find

the object 'MSmerge_ctsv_2C675AF5938E4EA1B7D0DCFF9ECAD9EB', because it does not

exist or you do not have permission.

sp_MS_marksystemobject:

Invalid object name '[dbo].[MSmerge_upd_2C675AF5938E4EA1B7

But this only happens when I add too many joined tables to the filter section of the publication.

Dan

Microsoft SQL Server 2005 RTM merge replication issues

We are trying to implement merge replication between 2 servers. Database size is around 11GB.

When I tried to create publication, snaphot agent is failing with following errror.

Error: 14151, Severity: 18, State: 1.
Replication-Replication Snapshot Subsystem: agent NBTENTSQL1X-PayDirectWeb-PayDirectPub-2 failed. The
replication agent had encountered an exception.
Source: Replication
Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
Exception Message: String or binary data would be truncated.
Cannot find the object 'MSmerge_sel_4D388

The table where the snapshot is breaking contains around 169 columns. Can someone please help me get through this error ?

Are you using vertical partitioning to replicate subset of the columns of the table? You may be hitting an known issue in Microsoft SQL Server 2005 where the list of column names for the table is longer than 4K and hence gets truncated in an internal proc.

|||

Thanks Sudarshan, your reply is very helpful. No we are not using vertical partitioning.

If this is a known issue, did Microsoft have any immediate fix for it or we have wait for SQL2K5 SP2 ?

|||Please wait for SQL2K5 SP2. You can install the latest CTPs availalbe and test out that your scenarios works. If not, please reply back and we can take a look further.|||This is a known issue in SQL2K5 SP1 which is fixed in SP2.|||Does the same limitation apply to the complexity of joined tables in the filter? I'm getting an error in the snapshot agent, saying:

String or

binary data would be truncated.

Cannot find

the object 'MSmerge_ctsv_2C675AF5938E4EA1B7D0DCFF9ECAD9EB', because it does not

exist or you do not have permission.

sp_MS_marksystemobject:

Invalid object name '[dbo].[MSmerge_upd_2C675AF5938E4EA1B7

But this only happens when I add too many joined tables to the filter section of the publication.

Dan

Metadata cleanup has deleted metadata at the Subscriber incorrectly?

Hi,

We have Microsoft SQL Server 2005 SP1 based merge replication application. This system consist of 1 publisher/distributor and about 700 subscribers. Publisher is MS SQL Server 2005 standard SP1 and subscribers are mostly MS SQL Server Express SP1s.

We started this system in September last year with migrating data as region based with one area at time. Everything went well until January 2007 when over 500 users were using new system. What is even strange that most errors comes on areas which were moved to new system on week number 2 on 2007 (8.1.2007->)

I have double and cross checked that these publications have exact the same parameteters as working publications.

The error message which comes on subscriber is following:

The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Subscriber for changes not yet sent to the Publisher. You must reinitialize the subscription (without upload). (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199401) Get help: http://help/MSSQL_REPL-2147199401

When this error comes client subscription should not be expired yet. So subscription is expiring incorrectly earlier than expected. After reinit subcriber can syncronize without any errors. For now machines suffering of this error have been different machines so there is no certain information if this error is repeatable on same machine.

Real bad thing is that if user has't syncronized in regular basis there is possibility to that data may be lost because the reinit must be done without upload as error message says.

Regards,

Jouko

We have now installed SP2 to over 650 machines and we are still getting this error. So it seems obvious that SP2 do NOT fix this error. We have now open second time support case to Microsoft about this issue. First time we got answer to apply SP2 to all machines that are involved with replication.

Jouko

Meta Data Cleanup For Merge Repl.

I am wondering specifically when the changes on the Publisher are cleaned
up. I have implemented merge replication with each publication having a
retention time (set in sp_addmergepublication) of 18 months and a
@.max_disretention (set in sp_adddistributiondb) also at 18 months. Also I
have a system where there is 1 Subscription PER Publication. The
Subscribers can be disconnected for long periods of time (I'm hoping for
less than 18 months). Now the changes on the Publisher will be stored on
the Distributor and will wait until a PULL Subscriber connects to Merge the
changes (changes made on both sides). From what I understand the merge
agent controls the meta data and will clean up the stored changes on the
distributor depending on the Retention period of the publication. So at
each merge agent run, anything older than 18 months that whether or not it
has been merged will be cleaned up (the max_disretention gets rid of changes
that have not been applied to subscribers too). Is this basically how the
cleanup of changes works?
When a connection is made I have a script that can be run to reinitialize a
subscription. It basically runs the merge agent, starts the snapshot agent,
sets the subscription to be reinitialized, and then runs the merge agent
again to apply the snapshot. Will Reinitializing a subscription clean up
the changes for a publication, kind of override the retention period?
Any ideas as to when the Change log (change info stored for a merge) is
cleaned up?
thanks,
Nate
Oops, the Distribution DB doesn't do anything during merge replication so
min_distretention and max_distretention don't apply. Just the Retention
period of the Publications. Can anyone confirm that the MetaData is only
cleaned up by the merge agent using the Retention period? And, that the
data is not cleaned up when a subscription is reinitialized?
thanks,
nate

Meta Data Cleanup Error

Hi,
We are Merge replicating about 50 Production databases. We had planned to
monitor the meta deta clean up error during replication.
While we were monitoring the data to be updated during replication in one of
these databases, noticed 4 records to be merge replicated ( 3 insert and 1
update). At this moment we noticed the meta data clean up error. "The merge
process could not perform retention-based meta data cleanup in database
'database name'". The replication was in retrying mode and additionally the
following errors were reported
a) The subscription to publication 'XXXX' is invalid.
b) The process is running and is waiting for a response from one of the
backend connections'
During this process there we no issues in the remaining 49 databases,
replication was proceeding as usual.
In this database where we noticed the the above errors, the replication was
in sync in another 10 minutes, without any manual intervention.
What triggers to raise these error messages.
Please let us know what correction action we need to take for these error
messages
Thanks,
Soura.
Hi,
I got it from One article...
"The most common reason I can think of is that the query timed out. Sometime
retention based cleaup may need to cleanup a lot of rows and that can take
time. Rerunning the merge with a larger value for the querytimeout parameter
should fix this problem."
Hope this will help
Herbert
"SouRa" wrote:

> Hi,
> We are Merge replicating about 50 Production databases. We had planned to
> monitor the meta deta clean up error during replication.
> While we were monitoring the data to be updated during replication in one of
> these databases, noticed 4 records to be merge replicated ( 3 insert and 1
> update). At this moment we noticed the meta data clean up error. "The merge
> process could not perform retention-based meta data cleanup in database
> 'database name'". The replication was in retrying mode and additionally the
> following errors were reported
> a) The subscription to publication 'XXXX' is invalid.
> b) The process is running and is waiting for a response from one of the
> backend connections'
> During this process there we no issues in the remaining 49 databases,
> replication was proceeding as usual.
> In this database where we noticed the the above errors, the replication was
> in sync in another 10 minutes, without any manual intervention.
> What triggers to raise these error messages.
> Please let us know what correction action we need to take for these error
> messages
> Thanks,
> Soura.

Message: Missing end comment mark '*/'.

Hi,

I created a web syncronization merge replication ,both servers and clients are sql server 2005 and everything is fine with the settings , but while synchronization and after i received some sch files i got the following message at the subscribers on the initial snapshot operation:

2006-06-27 07:50:14.471 The schema script 'myfile_311.sch' could not be propagated to the subscriber.
2006-06-27 07:50:14.471 Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script 'myfile_311.sch' could not be propagated to the subscriber.
2006-06-27 07:50:14.471 Category:NULL
Source: Microsoft SQL Native Client
Number: 113
Message: Missing end comment mark '*/'.

Any idea ?

Tarek Ghazali

SQL Server MVP

My guess is that myfile_311.sch contains a stored procedure|view|function with something like

/*

...

go

*/

There is a longstanding limitation in our batch parsing logic that will erroroneously recognize 'go's embedded in comments to be batch delimiters. The only workaround for this is to edit the script generated by the snapshot agent to remove the offending comment. We really hope that we can address this issue in the near future.

-Raymond