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

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

What is the maximum size of binary data (varchar(max)) that 2005 merge replication can tolerate

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


Since we have experienced creating 2005 ent SP2 merge replication with 60 GB binary data requires 90 hours to finish the whole process(snapshot job + merge job), we just worry that if we still increase the database size to fill in such large binary image and it will finally make merge replication malfunctioning. Just want to ask is there any limitation on image sizes or db sizes if  merge replication need to replicate them? Do u have benchmark testing or experiment that can prove on this upperlimit?

Thanks and regards,




View Complete Post

More Related Resource Links

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?




sql 2005 merge replication replicate 65 GB binary and cause "MSSQL_REPL20216 due to OS error 2"



We're using sql 2005 ent merge replication (SP2) and replicate 65GB binary data. However, after merge job running around 60 hours and nearly finished, it has the error messages like this :

From distribution.msrepl_errors and distribution.msmerge_history:

"The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an outfile to write" (Error Number: MSSQL_REPL-2147201001)

"The process could not read file "E:\Program files\Microsoft SQL Server\MSSQL.1\MSSQL\ReplData\unc\IMDNACMPS33_MPSERVER_MPSERVER\20110411220596\NoticeInfo_2.cft" due to OS Error 2(Error Number: MSSQL_REPL20216)


Please note that the above messages kept continuously appear in both event viewer and system tables between 1 minutes. Any suggestions or help to fix this?? Do I need to click 'stop synchronizing' and click 'start synchronizing' to restart the merge job again or click 'reinit subscription'? An urgent response is indeed necessary for this case. Thanks a lot.

Thanks and regards,

P.S: for your reference please find the attached error photo

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

Read Binary Data which is nothing but a Zip file and unzip through SSIS 2005 SP2

Hi ALL, I need some help in developing a task. I have a source database which is Oracle and it has a ZIP file stored inside the database in Binary format. When I move this data into the sql server 2005 database I get the data as binary data. Now the task begins with SSIS, I need to read the binary data which gives us a zip file and then unzip this zip file and read the XML data which is present inside the Zip file. I beleive some one might have already developed this task can you share the solution with us. Note: As this has to be moved into production I dont have permission to use third party tools like Cozy roc or install winrar.exe and simpy calling this exe from the execute process task in SSIS.  Raju

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:

Identity Range Value (Max Limit Finished) SQL Server 2005 (Merge Replication Problme)


Hi ,

I am using SQL Server 2005 enterprise edition, I Confirured the Merge Replication to my database). I have a replication of 21 database from various locations.

Problem is: When is trying to remove one subriction from the publisher and add a fresh subriciton to it. it is poping any error saying the Identity Range value exceed its Max Limit try to change from int to bigint. is there any solution for it not changing from int to bigint.

Thank you.


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

How to view merge replication error (sql 2005)


We're using 2005 merge replication and we found that some records deletion cannot synchronize from publisher table to other subscriber table. (This table have very small size(<1000 records). We want to view those replication system tables or any logs for diagnostics this serious problem. Can you suggest any replication system table or logs that can indicate the reasons or hints of records deletion outsync?

Thanks and regards,

2005 Merge Replication Conflicts



We have a republishing merge replication setup of SQL Server 2005 Head Office (HO) publishing to SQL Server 2005 Regional Offices (RO) re-publishing to SQL Server 2005 Express Stores (ST). The problem we are having is that of strange conflicts. They can be grouped into the following types:

1) Update conflicts where the data is the same on both sides and one side hasn't made a change since the last replication event.

2) Delete/Update conflict where the delete is real but the update isn't. Like #1 the update never occured.

3) Delete/Delete confluct where one of the deletes are real but the other isn't which then create a Unique identifer error in the conflict resolver.

We have looked over the SQL Server CU's both nothing describes our problem as we are running SP3. We've also search for other reported problems and can confirm that we don't have filters on DateTime fields only GUID fields and the join filter table hierarchy isn't deep. We've also tried a re-initialisation and verified that the collation is the same over every SQL instance.

Has anyone also come across and found a solution for this?

SQL 2005 Merge Replication Issue


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

SQL Server 2005 Schema Replication in Merge Replication


Dear Sir,

I am using SQL Server 2005 Developer Edition for Creating & Testing Database.

I had created Merge Publication WITHOUT FILTERING ROWS and Created Push or Pull Subscription. Now if I Add or Drop column using ALTER TABLE (T-SQL) in Table OR modify stored procedure using ALTER PROCEDURE in Published Database then while Synchronizing It replicate same in Subscriber Database and works very fine.

But when I had created Merge Publication WITH FILTERING ROWS (Parameterize or Static) and Created Push / Pull Subscription and I try to Add or Drop column using ALTER TABLE (T-SQL) in Table  OR modify stored procedure using ALTER PROCEDURE in  Published Database then while Synchronizing It DOESN'T REPLICATE in Table of Subscriber Database but tries Replicate in Stored Procedure and display error.

After Adding Column in Table of Published Database, If I Checked Article in property of Publication then it Adds column but it doesn't selects it. I also checked using sp_enumeratependingschemechenges it displays pending schema changes of stored procedure but

Merge Replication between SQL Server 2005 and SQL Server 2008



We are planning to implement Merge Replication between SQL Server 2008 "Not R2 Version" (Publisher) and SQL Server 2005 (Subscriber). Will this setup work, or there are some limitations ?

SQL 2005 Merge Replication : sysmergepublications unique index 'nc1sysmergepublications' fails

I've got one SQL 2005 SP2 server setup as Publisher and distributor which replicates through Merge pull subscriptions on SQL 2000 SP4 machines. Some of these SQL 2000 machines does republishing too. However, after a columndrop on one of the articles at the SQL 2005 machine by using sp_repldropcolumn the whole subscription sync came down on me with failures all over. All had the same error :

"Cannot insert duplicate key row in object 'dbo.sysmergepublications' with unique index 'nc1sysmergepublications'"
This procedure was called by sp_MScreateglobalreplica.

I have found that there are invalid publications in sysmergepublications, but only on publishers for the SQL 2000 subscription machines(excluding valid republishing publications). Deleting these pubID's does not help at all. With the first sync of any pull subscription (after the delete) they re-appear on the SQL 2005 machine.

However, I need to get the pull subscriptions going again. How can I clean the system tables to prevent any errors like above or duplicate keys in the sysmergesubscription table... Please do not recommend an overhaul. I need step by step procedures to get it going.

Merge replication setup with SQL EXPRESS 2005 on Windows 7 Professional


Hi all,

I have a web app that runs on local and use merge replcation using sql server 2005 standard on the server and sql express 2005 on clients on XP. As you know, XP Pro laptops are getting hard to find. We are trying to change to Windows 7 Pro x64. I installed SQL Express 2005 and I don't see a subscription on "Sync Center" after I created local subscription using SSMS. I normally see the subscription in Synchronize in XP Pro right away. 

I googled about this issue and not really have a good solution. There is one link saying Windows 7 not supporting Express 2005 anymore. http://blogs.msdn.com/b/repltalk/archive/2010/03/24/using-windows-synchronization-manager-on-x64-to-synchronize-replication-revised.aspx 

If so, I would like to know what would be a solution such as can I use Sql Express 2008 R2 instead of Sql Express 2005 for client even if the server is still SQL Server 2005 Standard edition. We are planning to upgrade, but we need to hire 5 to 10 people now. My boss does not want us to keep using XP Pro. 

Any suggestion would be appreciated.



Merge Replication Atomicity - at what point does ALL subscriber data become visible at the publisher



I have a Merge Replication installation going on with about 35 connected devices. When one of the subscribers synchronizes, do the inserted rows appear sporadically on the publication database at different times? Or do they appear all at once?

Can all of these be isolated inside a transaction - this would seem like it should be the default behaviour, in case of a sync being cancelled or failing due to loss of connectivity etc. 

The reason for this question is that another part of the system seems to have queried the publication database during and after a subscriber synchronization with differing results. 

If this is normal, what would be the best solution to ensure data integrity? 

Thank you in advance. 


Data not downloaded with SQLCE Merge Replication



I've got one problem with a merge replication and windows mobile device.

The database is stored on SQL Server 2005.
It is available with Internet via sqlcesa30.dll, hosted on an other computer.
The local db on Windows Mobile device is sqlce3.

Everything works fine since a lot of months.

But,since few weeks, some devices don't receive some rows.

I think it could be possible because the IIS server with sqlcesa30.dll had the LogOption to full and the actual log file was 800 mb.
I've flushed the file to be sure.

But, I don't know if it will be enough.

About the Event Viewer, the only thing I see is on the Server with SQL, i've got

Replication-Replication Merge Subsystem: agent 0030007B-0038-0044-3400-370042004500 failed. A read operation from the Internet returned an incorrect number of bytes. [,,,,,]


Replication-Replication Merge Subsystem: agent 0038007B-0039-0044-3700-320031003000 failed. De aanvraag voor het verzenden van gegevens naar de computer met IIS is mislukt. Zie HRESULT voor meer informatie. [,,,,,]

But, I do not have a lot of logs like this.

And i've got soo many devices that sync on this db (+-30 each 10 minutes) that it's not easy to know where is the problem.

Is someone has any idea?


PS : if found that the only solution

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