.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

Merge Replication Infinite Loop & Time Problem

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :

Hi all,

We have merge replication system between Sql Server 2008 R2 64bit Enterprise Edition (As a Publisher&Distributor) and Sql Server 2008 R2 Express Edition (As 800+ Subscribers). We use only "https " method as transfer method. We have 30 tables and we have Joins & Filters. Our system work normally for 5-6 months.

But for last 10 days, sometimes (generally initial snapshot being downloaded), it takes over 14 hours to replicate!! I think an infinite loop is being occured during the process. I trace the publication server with Sql Server Profiler 2008 and here is the trace file.

Lots of times, i see this line "exec sp_MSmergeupdatelastsyncinfo '8C01EA1F-153D-47BC-ACD3-F8439ACB7B76',2,N'Merge completed with no data changes processed .'" (and also other lines which repeat similar to this one) with same/different  Session ID's. And when i first see this line is after 3 seconds the start time of the Process!! And after , 6 seconds again same line, and after 13 seconds again same line and it goes on like this. I think, in fact synchronization process is finished after 5-6 seconds (normally because there is no any data change in fact!) but for some reason, it continues to same process over and over.

Is this a bug or anything else, h

View Complete Post

More Related Resource Links

Merge Replication and "Field size too large" problem

Hi,I have a single table database for tests configured on my server, I create another database using the same script that I use on my server on another server. I made a several inserts on the first database and I configure a merge replication between the two server and on the table article configure 2 integer columns to not be replicated and configure @pre_creation_cmd to none, because if I use another configuration on @pre_creation_cmd , when the snapshot applied it must recreated my table without the columns and I need then on another server. When I set @pre_creation_cmd  to none I need to create a rowguid column on the another server. My server is the Publisher and my another server is the publisher. I create the snapshot and when I sinconize the databases, the sinconization return that error:=================================================================================================The process could not bulk copy into table '"dbo"."CONSTS"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)Get help: http://help/MSSQL_REPL20037 Field size too large (Source: MSSQLServer, Error number: 0)Get help: http://help/0 To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below.  Consult the BOL for more information on the bcp utility and its supported options. (Source:

Need replication architecture assistance (near-real time merge repl across 2 separate companies and


I've read several different replication scenarios and technologies native to SQL Server (we are talking about SQL Server 2005) but have some questions.

I have a project to integrate/replicate two network-separated SQL Server 2005 databases.  Each database resides in a physically separate location at different companies.  They will not have a shared network environment and will not allow direct connection between the two databases.

We need to keep each database in sync as far as customer records.  Near real time is preferable.  There is not master.  One could add/delete/modify a customer record in one database and it needs to reflect in the other database. 


The closest we could get for connectivity architecture looks like this:

1.  Each database is on the separate internal networks for each company.

2.  Each organization HAS agreed to setup a DMZ on their sides specifically for this exchange.  Each respective DMZ will be able to communicate with their own internal SQL Server and have a fiber connect directly connecting each organization's dmz.

SO.... is there a good way to replicate from InternalSQL_1 - DMZ1 - DMZ2 - InternalSQL_2 near real time?

What would your recommendation be?  Pretty sure I'd setup both internal InternalSQL as Pub/Sub/Dist but I'm solici

Merge replication nested view problem

Using SQL 2005 as pub and SQL EXPRESS as sub using Merge replication. Got the following error message

The schema script 'CD_InTransit_v_153.sch' could not be propagated to the subscriber.
Error Detail:
The schema script 'CD_InTransit_v_153.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL20164)
Get help: http://help/MSSQL_REPL20164
Invalid object name 'dbo.Debit_v'. (Source: MSSQLServer, Error number: 208)
Get help: http://help/208

According to error message, it seems that debit_v is missing. However, I cannot control the sequence of view to replicate.  How can I solve this problem

How to minimize time when creating 2005 merge replication with very large data files and log files?


Dear all,

We're using window server 2003 sp1 + sql ent 2005 sp2 with merge replication between 2 servers. Since 1 of server sql is down, we need to create merge replication again. However, our data file size is 60Gb and log size is 40 GB and when we create merge replication it may need more than 10 hours to finish which is unacceptable since when creating replication the online db normal service operation may be affected. Is there any other way that can reduce the replication creation time within 5 hours to finish? Thanks a lot.


P.S: we are using full recovery model and plan to change to simple recovery model. Any help?




Merge Replication using Web Sync - Proxy Auto config problem



I am trying to setup merge replication using web sync. I  keep on getting the message: The Proxy Auto-configuration URL was not found. Yes I have read all the postings telling me to check my internet explorer settings concerning proxy server and auto-detection - I am not using a proxy - auto detection is unchecked.

Does anyone have another clue?



2006-08-16 15:31:09.375 Connecting to Subscriber 'CRAW'
2006-08-16 15:31:09.437 Connecting to Subscriber 'CRAW'
2006-08-16 15:31:09.453 The upload message to be sent to Publisher 'CRAW' is being generated
2006-08-16 15:31:09.453 The merge process is using Exchange ID '20E2BF4F-8812-431F-8BF8-94A44B80C16E' for this web synchronization session.
2006-08-16 15:31:09.500 The Proxy Auto-configuration URL was not found.

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?

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  

Real-time data replication

Are there any ways to replicate sql server production database to a standby database? I notice that there is a feature called 'continuous replication', does it do in real-time? what is the time-lag? Are there any potential data loss (i.e., production transaction committed but production db corrupted before the transaction is sent to standby)? Is the standby database available for read access during the replication?

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?

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

run-time error's do not jump to problem line but to assembly with some xml error.

  Used too, when there was a runtime error the ide would break at the line that caused the problem. Now I'm thrown into some assembly code with the error System.Windows.Markup.XamlParseException was unhandled   Message='The invocation of the constructor on type 'Main.MainWindow' that matches the specified binding constraints threw an exception.' Line number '4' and line   Which is extremely uninformative and inefficient. I can look down the chain to find the error thrown but this still is not very informative. How the heck can I get it back to the way it used to be? This has something to do with wpf and the assembly code has System.Windows.Markup.XamlReader.RewrapException as the address. I spend anywhere from 10x to 10000x as long because of this. If the code is simple then I still have to read the error code and click a button which takes time.   Also, I'd rather it break on the line and quit the process as it used to do. I don't need JIT debugging.  

Whats the reason for this infinite loop?

hi friends, i have the following fucntion that tokenize a CSV string and return a table of values create function fn_get_int_table_frm_csv ( @csv_values nvarchar(max) ) returns @indexes table (idx int identity(1, 1) primary key, Role_id int) as begin declare @ret nvarchar(max) declare @comma_pos int declare @token nvarchar(max) set @ret = @csv_values while len(@ret) > 0 begin if( len(@ret) <> 1 ) begin -- gets the first occurance of the "comma" in the @ret set @comma_pos = charindex(',', @ret); -- gets the first values that was seperated in the string set @token = substring( @ret, 1, @comma_pos - 1 ) -- add that to the return table insert into @indexes values ( convert(int, @token) ) -- set a new starting position set @comma_pos = @comma_pos + 1 -- Tokenize the string from the new starting position set @ret = substring( @ret, @comma_pos, len(@ret) - (@comma_pos - 1) ) end else begin set @token = @ret -- add that to the return table insert into @indexes values ( convert(int, @token) ) end end return end go when i execute this by passing '1,2,3,4' the argument, it goes to an infinite loop, how do i fix it? thanks

Strange for loop problem

Hi, I have this problem with moving a number of documents stored in a SharePoint Document Libray progamatically.The Document library is called "CR" which stores Change Management word documents.  Within this document library there may or may not be a folder which stores attachments (other documents, pictures etc) associated with the CR Document. When a button is pressed this code should see if the CR document has a corresponding folder, and then iterate through each file in that folder and move it to another archive folder.The code I have is as follows:SPList crlist = CR.Web.Lists["CR"]; for (int i = 0; i < crlist.Folders.Count; i++) { if (crlist.Folders[i].Folder.Name == "CR " + CR["CR Number"]) { for (int ii = 0; ii < crlist.Folders[i].Folder.Files.Count; ii++) { crlist.Folders[i].Folder.Files[ii].MoveTo(folderlist.RootFolder.ServerRelativeUrl + "/" + year.ToString() + "/" + WorkflowItem.Title + "/" + crlist.Folders[i].Folder.Files[ii].Name); } } }CR is list item (codument) which this code is working on at the time.crList is the sharepoint docu

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.
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