Merge Replication - Insert large number of rows RRS feed

  • Question

  • I have a database from which 500.000 rows were unintentionally deleted on one subscriber with the deletions duly replicated to the publisher on  to another subscriber. I found a recent backup, restored it to a temporary database then copied the deleted rows back to the table on the publisher, using an insert results query.

    That was successful and I sat back waiting for replication to occur. Merge Replication however seems unable to cope with a 500,000 rows insert. Instead of taking say 5 seconds, each attempt to replicate runs for about 30 minutes then fails with an error "MSSQL_REPL-2147200994". On most occasions less than a 100 rows are inserted.

    I assume that Merge Replication simply can't process that many rows that inserted in one transaction. Is there any way to get the replication to complete successfully? Should I have done something different with the inserts, broken them up into smaller transactions for example.

    I am beginning to think that the only option left now is to restart replication with a new snapshot from the publisher and re-insert the past few days records from the the active subscriber (having backed it up first).

    R Campbell

    Thursday, August 9, 2018 9:37 AM


All replies