none
Update conflict

    Question

  • I am running merge replication on SQL Server 2005. Included in replication are 6 Subscribers and one Publisher/Distributor. Replication compatibility level is SQL 2005 and I am using row-level tracking. Servers are synchronized in 5 minutes each (so gap between two consecutive syncs of one server is 30 minutes)

    Earlier all of this was on compatibility level SQL 2000, since all servers were upgraded from 2000 to 2005. But 10 days ago I upgraded and replication compatibility to 2005. I did not initialize subscription, since I had data in place and db's ready for merge replication.

    Since that 10 days ago I have column update conflict on each row change. Not just if it is changed at same time at different subscribers. For example, if I change one row today, and somebody else changes it three days later on other subscriber it counts as a conflict. He always chooses good (updated) version of data as winner, but it is quite annoying having 15000 conflicts in one table daily. And it makes harder to track other conflicts.

    Does it have anything to do with row level column tracking, or it is some known bug? Or, even better, if anyone knows how to avoid it. I was thinking about changing it to column level, but then I need to reinitialize subscriptions which means sending of 9 GB data through some pretty slow lines (256 kbp/s), or few hours work to drop existing subscriptions and create new one's. Maybe I would to this if I knew that column level will help.

    Tuesday, May 09, 2006 2:59 PM

All replies

  • Couple questions:

    for the table(s) that are constantly having conflicts, what does your subset filter clause look like?  I assume you're not using dynamic filtering?

    the updates that are made - what columns do they touch?  Is it the pk/fk column, or columns that exist in the subset filter clause, or merge join filter clause?

    I'm trying to determine if this is an existing issue we've seen in SQL 2005.

    Tuesday, May 09, 2006 6:24 PM
  • I am not using any filters since system user requirements were that they need "every single bit" of data on all locations. So I replicate everything.

    Updates do not touch pk/fk columns, and since I do not have filter, they are not in filter also.

    If this can help you, when I was migrating to SQL 2005 compatibility from SQL 2000, I dropped my subscriptions and publication since I needed to add few tables/fields to db (and to replication). But no field was added to tables with conflicts. As I said, later when I created new publication I added subscriptions using "Do not initialize"

    Thanks for effort, looking forward to next answer.

     

    Tuesday, May 09, 2006 10:50 PM
  • Since I don't have a copy of your schema or publication script, can you tell me what's special about the tables that are having conflicts?  Is it just a couple tables, or every table in the publication? 

    Are there any user triggers on the tables?

    Can you script out your publication and print it here?  Or just the publication and a couple of the articles which are constantly having conflicts?

    Are these SQL 2000 subscribers?

    Wednesday, May 10, 2006 8:12 PM
  • Are you using push or pull agents?  The topology that you currently have is Distributor and Publisher servers are SQL 2005 and Subscriber server is SQL 2000 or SQL 2005?

    Also wondering about the statement "needed to add few tables/columns to db".  When you use the "Do Not Initialize" option on new subscriptions, you need to have the same schema and data on both the publisher and subscriber.  Did the new tables and fields get added to the subscriber before the initial sync?

    Thanks,

    Tom

    This posting is provided "AS IS" with no warranties, and confers no rights.

    Wednesday, May 10, 2006 9:52 PM
  • I will probably supply you later with publication script, but here are answer to your questions.

    Conflicts are only at one table, and that is one of the most used tables. It has about 120000 records. I had again good look at conflicts and there are eactly one scenario.
    When somobody updates row at any of subscribers it propagates ok to Publisher. and then it logs conflict with each of other subscribbers. For winner it allways take right version (most recent update). But it takes no limits. If somebodu updates row which was updated last time in february, it counts as a conflict. If someone updates row last time updated one sync cycle ago, it also counts as a conflict.

    There are no user triggers on table. Subscribers are SQL 2005, and compatibility for replication is SQL 2005.

    Thursday, May 11, 2006 1:46 PM
  • No I am using jus push agents.

    Topology : Ditsributer and Publisher are One server (cluster) running SQL 2005, and they also have few clients conencted to them but they are not doing any data entry on table which causes troubles.

    When I said that I needed to add few tables, it ment to add them to database, since whole system is not static, it allways has new user requirements and that causes need  to sometimes add a table.

    For fields it is the same, you add fields to existing tables, but I didn't add any to troublemaker table.

    I used "Do not Initialize" and I am perfectly aware that data and schemas needs to be exact on each server. They were there I can assure you.  And I added new fields/tables prior to initial sync.

    This system is running replication since 01.06.2005. (almost a full year) and we have never encountered any issue that is not resolvable quickly. In a beggining it was SQL 2000 and then we upgraded our servers to SQL 2005 when it went live, and as I said, 10-15 days ago we just changed compatibility level of db and of replication to SQL 2005.

    Once again thanks to all for their efforts, and looking forward to fresh suggestions.
    I am also trying hard to figure this one.

    Thursday, May 11, 2006 1:56 PM
  • Aleksandar, thanks for your responses.  Last set of questions:

    It was working fine on SQL 2005 with SQL 2000 db compatibility, correct?

    As soon as you changed the db compatibility to SQL 20005, you started hitting conflicts, correct?

    The db compatibility was changed only on the published database, and not subscriber databases, correct?

    Thursday, May 11, 2006 3:42 PM
  • Yes, problems started somewhere with changing db compatibility level.

    All servers have same compatbility level - SQL 2005

    You asked for publication script here it is. Since I have 346 articels it was too big, so I am posting just 3 articles. Those are three tables with most updates, but only one of them (BUSVATRETURNS) is making problems.

    Also I am posting schema script for only table wich makes problems - BUSVATRETURNS.

    Publication:

    -- Enabling the replication database
    use master
    exec sp_replicationdboption @dbname = N'Alice', @optname = N'merge publish', @value = N'true'
    GO

    -- Adding the merge publication
    use [Alice]
    exec sp_addmergepublication @publication = N'Alice_HQ', @description = N'Merge publication of database ''Alice'' from Publisher ''HQ-VSQL''.', @sync_mode = N'native', @retention = 14, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @dynamic_filters = N'false', @conflict_retention = 14, @keep_partition_changes = N'false', @allow_synctoalternate = N'false', @max_concurrent_merge = 0, @max_concurrent_dynamic_snapshots = 0, @use_partition_groups = N'false', @publication_compatibility_level = N'90RTM', @replicate_ddl = 1, @allow_subscriber_initiated_snapshot = N'false', @allow_web_synchronization = N'false', @allow_partition_realignment = N'true', @retention_period_unit = N'days', @conflict_logging = N'both', @automatic_reinitialization_policy = 0
    GO


    exec sp_addpublication_snapshot @publication = N'Alice_HQ', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 500, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 0, @publisher_login = N'sa', @publisher_password = N''
    exec sp_grant_publication_access @publication = N'Alice_HQ', @login = N'sa'
    GO
    exec sp_grant_publication_access @publication = N'Alice_HQ', @login = N'BUILTIN\Administrators'
    GO
    exec sp_grant_publication_access @publication = N'Alice_HQ', @login = N'UINO\SQLAdmins'
    GO
    exec sp_grant_publication_access @publication = N'Alice_HQ', @login = N'UINO\sqladmin'
    GO
    exec sp_grant_publication_access @publication = N'Alice_HQ', @login = N'distributor_admin'
    GO

    -- Adding the merge articles
    use [Alice]
    exec sp_addmergearticle @publication = N'Alice_HQ', @article = N'ACCOUNTTRANSACTIONSVAT', @source_owner = N'dbo', @source_object = N'ACCOUNTTRANSACTIONSVAT', @type = N'table', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'none', @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'', @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0
    GO


    use [Alice]
    exec sp_addmergearticle @publication = N'Alice_HQ', @article = N'BUSDETAILS', @source_owner = N'dbo', @source_object = N'BUSDETAILS', @type = N'table', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'none', @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'', @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false',

    use [Alice]
    exec sp_addmergearticle @publication = N'Alice_HQ', @article = N'BUSVATRETURNS', @source_owner = N'dbo', @source_object = N'BUSVATRETURNS', @type = N'table', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'none', @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'', @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0
    GO

    Table:

    USE [Alice]
    GO
    /****** Object:  Table [dbo].[BUSVATRETURNS]    Script Date: 05/11/2006 18:24:10 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[BUSVATRETURNS](
     [SID] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
     [CODE] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
     [PERIOD] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
     [PERIODSTARTDATE] [datetime] NULL,
     [PERIODENDDATE] [datetime] NULL,
     [SHEETNUMBERSID] [int] NULL,
     [DATEOFRECEIPT] [datetime] NULL,
     [TAXABLESUPPLY] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_TAXABLESUPPLY]  DEFAULT (0),
     [TAXABLEPURCHASES] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_TAXABLEPURCHASES]  DEFAULT (0),
     [EXPORTS] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_EXPORTS]  DEFAULT (0),
     [IMPORTS] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_IMPORTS]  DEFAULT (0),
     [NONTAXABLESUPPLIES] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_NONTAXABLESUPPLIES]  DEFAULT (0),
     [EXEMPTPURCHASE] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_EXEMPTPURCHASE]  DEFAULT (0),
     [NONBUSPURPOSE] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_NONBUSPURPOSE]  DEFAULT (0),
     [PURCHASEOFCAPITALGOODS] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_PURCHASEOFCAPITALGOODS]  DEFAULT (0),
     [VATCHARGEDTOREGISTEREDTRADERS] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_VATCHARGEDTOREGISTEREDTRADERS]  DEFAULT (0),
     [VATPAIDTOREGISTEREDTRADERS] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_VATPAIDTOREGISTEREDTRADERS]  DEFAULT (0),
     [IMPORTVATRECLAIM] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_IMPORTVATRECLAIM]  DEFAULT (0),
     [VATFEDERATION] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_VATFEDERATION]  DEFAULT (0),
     [VATRS] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_VATRS]  DEFAULT (0),
     [VATBRCKO] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_VATBRCKO]  DEFAULT (0),
     [CARRYOVER] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_CARRYOVER]  DEFAULT (0),
     [OUTPUTVATTOTAL] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_OUTPUTVATTOTAL]  DEFAULT (0),
     [INPUTVATTOTAL] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_INPUTVATTOTAL]  DEFAULT (0),
     [NETVATLIABLILITY] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_NETVATLIABLILITY]  DEFAULT (0),
     [EXCESSCREDIT] [int] NULL,
     [REFUNDREQUEST] [bit] NOT NULL,
     [PLACE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_BUSVATRETURNS_PLACE]  DEFAULT (''),
     [RECEIVEDDATE] [datetime] NULL,
     [PREVIOUSRETURNSHEET] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_BUSVATRETURNS_PREVIOUSRETURNSHEET]  DEFAULT (''),
     [VERSIONNO] [int] NULL,
     [COMMENTS] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_BUSVATRETURNS_COMMENTS]  DEFAULT (''),
     [BATCHNO] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     [EXPECTEDRETURNDATE] [datetime] NULL,
     [CONTROLNO] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_BUSVATRETURNS_CONTROLNO]  DEFAULT (''),
     [BAND_INPUT] [int] NULL,
     [BAND_OUTPUT] [int] NULL,
     [BAND_TOTAL] [int] NULL,
     [FILLINGREFERENCE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__BUSVATRET__FILLI__2EB7FF2E]  DEFAULT (''),
     [RETURNTYPE] [int] NULL CONSTRAINT [DF__BUSVATRET__RETUR__46C55E40]  DEFAULT (0),
     [FARMERSSUPPLIES] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_FARMERSSUPPLIES]  DEFAULT (0),
     [FARMERSLUMPSUM] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_FARMERSLUMPSUM]  DEFAULT (0),
     [BOXNETOUT] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_BOXNETOUT]  DEFAULT (0),
     [BOXNETIN] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_BOXNETIN]  DEFAULT (0),
     [BOXCLAIMIN] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_BOXCLAIMIN]  DEFAULT (0),
     [BOXCLAIMOUT] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_BOXCLAIMOUT]  DEFAULT (0),
     [RETURNSTATUS] [int] NULL CONSTRAINT [DF_BUSVATRETURNS_RETURNSTATUS]  DEFAULT (0),
     [VATLOIN] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_VATLOIN]  DEFAULT (0),
     [VATMIDIN] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_VATMIDIN]  DEFAULT (0),
     [VATHIIN] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_VATHIIN]  DEFAULT (0),
     [VATLOOUT] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_VATLOOUT]  DEFAULT (0),
     [VATMIDOUT] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_VATMIDOUT]  DEFAULT (0),
     [VATHIOUT] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_VATHIOUT]  DEFAULT (0),
     [ALICE_ROWSTATUS] [tinyint] NULL,
     [ALICE_CREATEDBYUSERID] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_BUSVATRETURNS_ALICE_CREATEDBYUSERID]  DEFAULT (''),
     [ALICE_CREATEDTIMESTAMP] [datetime] NULL,
     [ALICE_CREATEDONMACHINE] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_BUSVATRETURNS_ALICE_CREATEDONMACHINE]  DEFAULT (''),
     [ALICE_CREATEDBYPROGRAM] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_BUSVATRETURNS_ALICE_CREATEDBYPROGRAM]  DEFAULT (''),
     [ALICE_UPDATEDBYUSERID] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_BUSVATRETURNS_ALICE_UPDATEDBYUSERID]  DEFAULT (''),
     [ALICE_UPDATEDTIMESTAMP] [datetime] NULL,
     [ALICE_UPDATEDONMACHINE] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_BUSVATRETURNS_ALICE_UPDATEDONMACHINE]  DEFAULT (''),
     [ALICE_UPDATEDBYPROGRAM] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_BUSVATRETURNS_ALICE_UPDATEDBYPROGRAM]  DEFAULT (''),
     [ALICE_CHECKSUM] [numeric](38, 8) NULL CONSTRAINT [DF_BUSVATRETURNS_ALICE_CHECKSUM]  DEFAULT (0),
     [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF__BUSVATRET__rowgu__2315D498]  DEFAULT (newsequentialid()),
     CONSTRAINT [PK_BUSVATRETURNS] PRIMARY KEY CLUSTERED
    (
     [SID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO
    USE [Alice]
    GO
    ALTER TABLE [dbo].[BUSVATRETURNS]  WITH CHECK ADD  CONSTRAINT [FK_BUSVATRETURNS] FOREIGN KEY([CODE])
    REFERENCES [dbo].[BUSDETAILS] ([CODE])
    ON UPDATE CASCADE


    GO
    ALTER TABLE [dbo].[BUSVATRETURNS]  WITH CHECK ADD  CONSTRAINT [FK_BUSVATRETURNS_BN] FOREIGN KEY([BATCHNO])
    REFERENCES [dbo].[BUSRETURNBATCH] ([BATCHNO])

    Thursday, May 11, 2006 4:46 PM
  • Hi Aleksandar,

    I am trying to create Replication Topology (Merge Replication) like below.

    Subscriber1 --> Publisher  <-- Subscriber2.

    I have created both subscribers with Subscription Type as Server with Priority as 75.      I am updating the Column A of Row_10 in Subscriber1 on time say 11 am. After i am running the Starting synchronizing agent from  Subscriber1. The value propagated to Publisher now publisher contains the latest value in Column A.  Uptonow the Subscriber2 is not synchronized with Publisher.

    Now in Subscriber2 also Column A of Row_10 is updated say at 11.10 am. Actually now Publisher contains the value from Subscriber1 for that Column and in Subscriber2 we have the same column updated.

    Now i am running the Synchronization in Subscriber2, i am getting the result which is not expected. Here Publisher's value is propagated to Subscriber2. But as per real scenario Subscriber2 has the Latest value which is updated on 11.10 AM.  

    I don't know what am i missing here. Actually merge replication should see the time stamp and it has to decide winner. But here it always considers publisher as a winner and puts the data to Subscriber.

    Can you help ? 

    thanks in advance.

    Saturday, May 13, 2006 8:44 AM
  • Thams,

    I'll try to help. Data on subscriber2 is changed to data on publisher (if I get you right) instead of being the other way around, but server have almost certainly logged conflict on your publication. You can view conflicts using Conflict Viewer (if you use SQL 2005 - right click on Publication name in Replication - Local publication, and choose View Conflicts). There you will be able to resolve conflict as you wish. In your case, you should choose submit looser. If you choose submit winner, that means that you agree with server choice. If you use priority resolver - and it seems that you do be aware that Publisher ones he gets data, he takes ownership of that row and then it has highest priority then subscribers, that is why he resolved conflict in a way that he did.

    I have never tried (I always use priority resolver), but I guess that if you choose custom resolver that there is some kind of custom time stamp resolver supplied with SQL server.

    I hope this helps,

    Aleksandar

    Sunday, May 14, 2006 5:53 PM
  • Aleksandar,

    Thanks for the reply. Actually I found solution. We can use Microsoft Provided Custom Resolver for this. DateTime Later Wins resolver. I think using this my issue will be resolved. But i didn't try this resolver yet.

    Friday, May 19, 2006 1:36 PM
  • Thanks for posting so much information.  Based on the information on the thread I tried to set up a scenario to repro the issue.

    With SQL 2000, I created a merge publication with a couple table articles that use row-level tracking for conflicts.  I also created four push subscriptions.  I processed some update DML to verify non-conflicting updates worked as expected and then processed some conflicting update DML to verify it works as well. 

    Next, I upgraded everything to SQL 2005 RTM.  After the upgrade, I verified snapshot and all merge agent sync's still worked.  I also processed some non-coflicting and conflicting update DML to verify things are still working as expected.

    Now I got to the point where I wanted to change the merge publication compatibility mode from SQL 2000 to SQL 2005.  Just like you did, I dropped the existing push subscriptions and publication, then recreated the merge publication with publication compatibility mode equal to SQL 2005.  Also, added the table articles back with row-level tracking.  The push subscriptions were recreated, all with the "do not initialize" option.  After the setup was complete, I executed the snapshot and each merge agent sync.  I again processed some non-conflicting update DML and conflicting update DML.  All update transactions were handled as expected.  Is there anything else special about the scenario that I left out? 

    The problem that I was hoping to reproduce was that non-conflicting updates made at the subscriber nodes would end up causing conflicts that should not have arisen.

     

    It seems like you can easily get a repro on your system.  Could you please post the following information.  Cause an unexpected conflict to happen, then at the publisher query the MSmerge_conflicts_info table based on the rowguid of the row you just forced an unexpected conflict to occur to.  Post this information, I'd like to see what the conflict_type, reason_code, and reason_text is for this conflict.  Also based on the rowguid and origin_datasource_id of that record, you can query the MSmerge_conflict_<Pub Name>_<Article name> table to see if the conflict loser infomation provides any more clues as to why this happens.

    Are you sure one of the nodes in your topology does not have a process that may be touching all rows in the table in question.  Perhaps there is a mass update process or cacading action of some sort that could be causing this to happen?

    Thanks,

    Tom

    This posting is provided "AS IS" with no warranties, and confers no rights.

     

    Friday, May 19, 2006 8:40 PM
  • Tom,

    thanks for effort, I wiill try to get data from MSmerge tables as soon as possible.

    Only thing that you maybe missed in your reproducing effort is changing db compatibility from 2000 to 2005.

    I was also suspecting that there is some piece of code in application that runs on this db, that would cause all rows to be touched so conflicts would accured.First, I reviewed pieces of code which could be suspicios, and then I runned SQL Profilier for half a day on one of servers. After both analysis, results were negative, and there were no indications that something like that is hapenning.

    Aleksandar

    Saturday, May 20, 2006 8:31 AM
  • Tom,

    I did as you asked and queried conflicts info table. These are results for 1 rows in csv:

    tablenick, rowguid, origin_datasource, conflict_type, reason_code, reason_text, pubid, MSrepl_create_time, origin_datasource_id

    21402017,077A10C2-73E6-DA11-9098-00142208823E,bl-sql-srv.Alice,1,1,The same row was updated at both 'HQ-VSQL.Alice' and 'bl-sql-srv.Alice'. The resolver chose the update from 'HQ-VSQL.Alice' as the winner.,7FCE5FEA-1943-4C88-8CF0-3873F0AC2A0F,2006-05-25 08:21:40.263,206113F6-8678-4E05-AD92-F9CB3781F06C

    21402017,077A10C2-73E6-DA11-9098-00142208823E,mo-sql-srv.Alice,1,1,The same row was updated at both 'HQ-VSQL.Alice' and 'mo-sql-srv.Alice'. The resolver chose the update from 'HQ-VSQL.Alice' as the winner.,7FCE5FEA-1943-4C88-8CF0-3873F0AC2A0F,2006-05-24 13:48:01.633,AC5F9646-7B41-42DA-AAF4-55420B7C9889

    21402017,077A10C2-73E6-DA11-9098-00142208823E,sa-sql-srv.Alice,1,1,The same row was updated at both 'HQ-VSQL.Alice' and 'sa-sql-srv.Alice'. The resolver chose the update from 'HQ-VSQL.Alice' as the winner.,7FCE5FEA-1943-4C88-8CF0-3873F0AC2A0F,2006-05-24 13:57:29.640,3CE38FA1-1F66-4F17-8D27-F31E3A731FA2

    21402017,077A10C2-73E6-DA11-9098-00142208823E,tz-sql-srv.Alice,1,1,The same row was updated at both 'HQ-VSQL.Alice' and 'tz-sql-srv.Alice'. The resolver chose the update from 'HQ-VSQL.Alice' as the winner.,7FCE5FEA-1943-4C88-8CF0-3873F0AC2A0F,2006-05-24 14:04:18.407,8F257FFF-D67A-4FFB-B448-675179006CEF

    Also when I query MSmerge_conflict_<Pub Name>_<Article name>  table based on rowguid and origin_datasource_id as I result I got rows with same values on all servers (as it should be)

    Notice that first server is a bit late because it's network didn't work until this morning, but it is not something that affects this behaviour. Also, in "list" there is no server on which update is actually maked, which makes perfst sense.

    What I have noticed from this is that new server which I have added during last three days to test with all same options exept I used initializing (he was on 1 gigabit line so he initialized pretty fast, altough db is large) do not have any conflicts. Is there really possibility that using "Do not initialize"  have some bug in 2005 and id doesn't work good as in 2000? (In 2000 it was option "subscriber already have schema and data" - or something like that)

    Do you have any other idea how to check what's happening? Thanks,

    Aleksandar

     

    Thursday, May 25, 2006 9:57 AM