.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Is there no need to manually clean up merge replication system table?

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :


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

View Complete Post

More Related Resource Links

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?

Merge replication unique id of table

Hi guys,I am designing a CRM solution using SQL Server 2008 and windows forms.The solution should allow people to work in a disconnected mode.I would like to ask for an advice about the best practice for implementing the solution.Currently I am expecting to have 1 central server with about 20 small clients.When the connection between the server and the clients falls the clients should work in disconnected mode persisting all changes to a local database. So on the Server I will use SQL Server 2008 Enterprise and for the clients SQL Server Express.I am planning to configure merge replication between the server and the clients and have the clients operate against the server until the connection drops.One question that I have in particular is what to use for ids of my tables ? I am currently using identity columns and planning to assign a range to each client and server so that ids do not overlap.But I am wandering whether not to use custom id for example a varchar(15) for example. Then I can easily assign a prefix for each client and ensure uniqueness of ids. I have also read that SQL Server adds a GUID to each table anyway so I wandered if using a GUID is a good idea ? I appreciate your answers.Regards,kzmp

Merge replication - table with geography data type



I have a problem with geography data type replication. Any change that is done on a Geography column breaks the replication - the error message is "The Merge Agent failed because the schema of the article at the Publisher does not match the schema of the article at the Subscriber." I double checked compatibility settings that is suggested here: http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/d8e31621-5d43-48cd-bdd3-c02b8a24625c. But both publisher and subscriber are 100RTM.

I checked synchronization logs and found a strange message - attempt to convert varchar to geography. I don't understand from where the varchar data type comes from. Tables at subscriber and publisher has the same format - geometry stored in geography data type.

2010-09-20 18:36:22.499 The Merge Agent failed because the schema of the article at the Publisher does not match the schema of the article at the Subscriber. This can occur when there are pending DDL changes waiting to be applied at the Subscriber. Restart the Merge Agent to apply the DDL changes and synchronize the subscription.
2010-09-20 18:36:22.845 Category:NULL
Source:  Merge Replication Provider
Number:  -2147199398
Message: The

table schema changes not being propagated - merge replication


I have attemped to change column types (i.e. from int to varchar(8)) on read-only published tables. I used ALTER TABLE ALTER COLUMN to make the change, and according to BOL this should make sure the changes are seen at the subscribers. Unfortunately, after I make the change to table and run sp_enumeratependingschemachanges, I do not see the table changes listed. However I can make changes to SPs and UDFs, and they do show up in sp_enumeratependingschemachanges and on the subscriber. Any ideas would be appreciated.
The publication is marked for replicating schema changes, the publisher is SQL 2005 and the subscribers is SQL 2005 Express btw.


Darrell Young

Applying Push Merge Replication snapshot manually


Here is the complete scenario :- SBS 2003 running Sql Server 2005 Standard, Merge Replication and Push Subscription. Connection Via VPN. Subscribers are SQL SERVER 2005 express..

Sometimes due to some structural changes, snapshot is to be re-applied. This is rare but is possible in our case.. On some subscribers snapshot applying goes on for hours and days with no sucess.

Are there any step by step instructions on how we can reintialize or reapply the snapshot on those subscribers manually i.e. maybe by copying the replication folder manually to subscriber and applying manually..

Pls help..

IMP : There might be a delay in posting replies due to time difference (GMT +2:00) and Friday's Holiday instead of Sunday.

Merge Replication - Add table to existing publication


I added a table to an existing publication and then generated a new shapshot.  The merge agents then sent the new table and data down to the sunscribers as planned.  The issue is that at the next cycle the merge agent re-initilizes the table again.  This happens over and over again.  If I stop and restart the agent I can watch it send the schema back down to the subscriber.  Any thoughts are appreciated.



Steps by Step document for configuration Merge Replication in 2005

Hi , Can anyone provide link to configure Merge Replication in 2005 with print screen.Also let me know the difference between Transcation level and Merge Replication. regards Vijay  

SQL Server 2005 Merge Replication

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

Merge Replication: How to give read access on snapshot share to a sql account

Hello All, I want to give read access to an sql account(not windows account). Can it be given or not. Someone please tell. Thanks saandii777

fragmentation in merge replication

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?

unable to create new table in sql server 2000 enterprise manager on windows7(Operating system)

i have successfuly installed sql server 2000 on windows7 and i am able to browse all the tables and edit aswell(on enterprise manager) but i have a problem while creating new tables it says ' MMC has detected an error in a snap-in' and provides options to either continue with the error or send a report to microsoft ,   Please help regarding this compatibility issue   Thanks in advance !

Merge Replication: Fails using Replication.SaveProperties when changing Subscription Info

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

configuring merge replication

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

Merge Replication, Push Subscription : The snapshot takes centuries to apply

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.

error in merge replication

Hi ALL,      I have merge replication configured between republishers( sql 2008) and subscribers( sql 2000)...i am getting the following error while trying to sync   The merge process could not update the list of subscriptions. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201010) Get help: http://help/MSSQL_REPL-2147201010 Cannot insert duplicate key row in object 'dbo.sysmergesubscriptions' with unique index 'uc1sysmergesubscriptions'. (Source: MSSQLServer, Error number: 2601) Get help: http://help/2601 The subscription could not be created. (Source: MSSQLServer, Error number: 14057)   Any suggestions y i am getting this error TIA

2 way (bidirectional) MERGE replication?

Is this possible? I have SQL Server 3.5 CE on a windows mobile device which is going to do the majority of collection of information. I have SQL Server 2008 (needs to work with 2005 too) on a server and have no problem getting data from the server to the mobile device via replication but I need to know if it's possible and if so how, to get the merge to work the other way at the same time... or even via another means if it must do. Thanks for any help. Steve   It might also be helpful to add here that the server is configured for web synchronisation.

SSRS2005: Is it safe to manually remove instances from the Keys table in the report server database?

I have a problem with redundant reporting services instances still hanging around in the initialization pane of the RS configuration wizard and I can't get rid of them. The problem arose when we had new webfarms and before I had removed all references to the old ones from the initialization screen, the old webfarms had been switched off, removed from the network and dismantled. Therefore when I try to 'de-initialize' them, I get an error stating that reporting services cannot connect to them and when I run reports, a fair few are trying to run on the old webfarms which no longer exist and are disappearing into the void. There's a table in the report server database called Keys whch contains details of these intances and i'm wondering if its safe to remove these via SQL commands without breaking reporting services on other machines.   As a side note: All this stems from the fact that when you uninstall Reporting Services, not all of the files / references are removed. So as a general word of warning, if you do an uninstall of RS2005, you'll need to make sure everything's gone before re-installing, throwing away old machines etc. These include folders in IIS and these old instances as mentioned above.  
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend