merge replication system tables fragmentation issue

I have Merge replication going on in my environment. The problem is that system tables like MSmerge_content, MS merge
_tombstone and MSmerge_genhistory are getting fragmented. We are rebuilding indexes time to time daily. But we want some good fix. Just send some good idea guys?

More Related Resource Links

I have Merge replication going on in my environment. The problem is that system tables like MSmerge_content, MS merge _tombstone and MSmerge_genhistory are getting fragmented. We are rebuilding indexes time to time daily. But we want some good fix. Just
send some good idea guys?

Ihave merge replication set up between two sites. Everything worked fine up until the subscriber wanted to update the publisher. I get the following error:

The merge process is retrying a failed operation made to article 'X' - Reason: 'The Merge Agent was unable to synchronize the row due to one or more unanticipated errors in the batch of changes. When troubleshooting, increase the -OutputVerboseLevel setting,
restart the agent, and check for and resolve any errors generated by the database engine. '.

I have configured transactional replication on 7 databases from an sql server 2005 to another sql server 2005.

One of them has 487 tables and when I check "Replication Monitor" it shows that everything ok, but database above , that shows the message "Performance Critical".

My question is : The tables have been replicated or not ? How can I check this ? It´s not possible check table per table. Anyone suggests any other way to check if tables and their data have been replicated.

I've been fighting with merge replication for the past few days over the same hurdle and I can't seem to figure this one out. I create the publication that publishes 36 articles (28 of which are download only). After creating the publication,
I can replicate perfectly normal as expected. When replicating, I pass in a project number through the host_name() variable and filter the data based on that value and all works great. I've replicated about 10 different projects, modified data,
and merged the changes back without issue.

Where I'm running into an issue is when I add a new project to the mix. I have an SSIS project that takes data and puts the data into a staging area. From there, I have an ETL that pulls the data from the staging area and inserts it into the
the database that is set up with merge replication. Being that these new records will need to be replicated in the future, I do a simple insert into, select from so that the replication triggers are fired and these rows are tracked.

After the data load (~150,000 rows in total among 5 tables), I go to replicate this new project however it never gets past the step

"Retrieving subscription information."

It eventually times out and never transfers the data. When I go back and try to rep

In my
previous post, Hilary replied that the size of MSmerge_Contents doesn't really matter, but index fragmentation does. However, referring to
BOL, it says, "Additionally, you should minimize the size of the merge system tables (specifically MSmerge_history) by using
sp_mergecleanupmetadata." Is it still necessary to do that in SQL Server 2005?

Also, when running select metadatacleanuptime
from sysmergesubscriptions, I found that the cleanup time is just about a week before. However, my merge interval is only 5 minutes, and I remembered during last week I did a manual sp_mergemetadataretentioncleanup although
paused in half way. How to check the clean up interval?

Last but not least, I have already did index rebuild on MSmerge_Contents, MSmerge_henhistory, and MSmerge_tombstone, and found that the average fragmentation dropped from above 90% (MSmerge_Contents) to below 1%, but the overall performance did not improve
obviously. I remembered when merge replication was set up at the begining, th

After checking system.indexes, I found that the fill_factor of 4 indexes of msmerge_contents are all 0. Does this mean I do not need to specify the value of FILLFACTOR? How about another 2 parameters, i.e. SORT_IN_TEMPDB and STATISTICS_NORECOMPUTE?

Hi thre,
My target server keeps running sp_MSupd sp for different values in profiler and synch status shows "delivering replication commands"
I keep waiting but this never ends. and ultimately we run a snapshot and it can't be applied.
So recreate the whole replication again. It runs fine for a while but again, the sp_MSupd sp starts running on the target server and the replication goes down again.
I don't know how to fix this. any advices?
Regards

We did the following:
We setup a database with all of standard tables, views, etc.
We then put in place merge replication for a few of the tables within this database, including a tables called "Areas".
We can execute SQL statements to insert rows into this table fine.
We then applied an update script for this database to change a few tables by adding fields and changing indexes, etc. We didn't change the "Areas" table though.
After the update to the database structure, we get an error when trying to insert into this "Areas" table.
The error message was the following:
The insert failed. It conflicted with an identity range check constraint in database 'AETest', replicated table 'dbo.Areas', column 'AREPrimaryId'. If the identity
column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent. The statement has been terminated.
What would cause such a problem and how do we resolve it? The structure of the "Areas" table before and after the update is the following:
USE
[AETest]
GO
/****** Object: Table [dbo].[Areas] Script Date: 08/24/2010 17:42:36 ******/
SET
ANSI_NULLS
ON
GO
SET
QUOTED_IDENTIFIER
ON
GO
SET
ANSI_PADDING
ON

I'm trying to use SQL Server/Compact Framework 3 Merge Replication functionality and am running into a problem when calling Replication.SaveProperties (using c#). I was hoping to get some clarification on whether its my interpretation on how things should
work vs a coding problem.
Basically, the Merge Replication seems to work as I can sync information between my handheld and server databases. However, I've created a form on my handheld so that the user can change some synchronication settings (e.g. Publication or Publisher
Name) and then save it.
What I've noticed is :
1) You can NOT call Replication.SaveProperties until after a synchronization is performed (otherwise you get an error message). Assuming this is so that it can create the "__sysMergeSubscriptions" table. Does this sound right?
2) If I then try to change the saved Subscriber information via another call to Replication.SaveProperties (e.g. change the Publication info), I then get an error message that says:
The subscription was not found. Publisher, PublisherDatabase, or Publication could be incorrect.
Is this normal behavior? Does this mean that you can you NOT change subscriber information; only add or drop it?
Thanks,
Ed

Hi ALL,
I am configuring merge replication... For example
Publisher A sql server 2008 ( databse adventureworks)
subscriber B sql server 2000 ( database adventureworks)
I already have database created at subscriber....both databases at publisher and subscriber are identical in structure but changes in data( at subscriber there some inserts and updates)
now before i create new snapshot and applied to subscriber i want to upload changes which are at subscriber... how can i apply changes to publisher database before i run merge agent...
TIA

Well, not centuries, except that the users are storming the gate.
I'm trying to find how to get the snapshot moved to the subscriber and applied in a reasonable time. Last time was successful, but took 2 1/2 days to build the subscriber database from the snapshot.
Hillary responded:
Something is very wrong here.
You should be able to generate your snapshot, copy it manually over to the subscriber - using the altsnapshotfolder parameter and then apply it there.
So now I have my snapshot, a folder with lots of .cft .bcp .dri .prc .sch and .trg files. Getting this to the subscriber computer shouldn't take long. Once I get it there, how do I use it to get the subscriber set up?
You can't be successful at this unless you're at least 1/2 a bubble off level.