1

Can anyone please help me out to solve this error in SQL Server merge replication?

The merge process could not replicate one or more INSERT statements to the 'Subscriber'. A stored procedure failed to execute. Troubleshoot by using SQL Profiler. (Source: MSSQLServer, Error number: -2147200990)

A query executing on Subscriber 'DB-TEST' failed because the connection was chosen as the victim in a deadlock. Please rerun the merge process if you still see this error after internal retries by the merge process. (Source: MSSQLServer, Error number: 20245)

Cannot issue SAVE TRANSACTION when there is no active transaction. (Source: MSSQLServer, Error number: 628)

Community
  • 1
  • 1
Ahsan Mumtaz Abbasi
  • 53
  • 1
  • 11
  • I would do what the error message suggests: start SQL Server Profiler and trigger the merge replication manually using Replication Monitor then find the failed spT call in the SQL Profiler log, copy and paste it into SQL Server Management Studio and see what actual errors that failing spT returns. Without these detailed error messages and without your DB structure as well as the replication config it's difficult to guess what is wrong. – andrews Sep 05 '17 at 08:01
  • Thank you sir for you kind reply.... kindly review the error log... – Ahsan Mumtaz Abbasi Sep 05 '17 at 08:21
  • 2017-09-01 15:40:44.310 OLE DB Subscriber 'DB-test': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,...) 2017-09-01 15:40:44.316 [4%] [8078 sec remaining] OLE DB Subscriber 'DB-test': {?=call dbo.MSmerge_upd_sp_5C14F4D4AE344104F78DB536C31A47C3_batch (?,?,?,?,?,?,?,?,,...) 2017-09-01 15:40:12.661 Percent Complete: 4 2017-09-01 15:40:44.319 A query executing on Subscriber 'DB-test' failed because the connection was chosen as the victim in a deadlock. Please rerun the merge process if you still see this error after internal retries by the merge process. – Ahsan Mumtaz Abbasi Sep 05 '17 at 08:23
  • Looks like your subscriber connects to publisher using OleDB and the sync takes too much time or there is too much data to sync and the connection is too slow, notice it's stalled at 4%. So you need to troubleshoot the OleDB connection between your subscriber and publisher. – andrews Sep 05 '17 at 08:47
  • Thank You Sir, Actually i'm using intranet SQL Server 2014 for both (Subscriber & Publisher) with (LAN) between my publisher & subscriber but if u can help me to troubleshoot the OleDB Connection means like give a hint the point to look. that will be good sir, – Ahsan Mumtaz Abbasi Sep 05 '17 at 09:09
  • Sir any Suggession? – Ahsan Mumtaz Abbasi Sep 06 '17 at 04:58
  • Did you try manually running replication sync via Replication Monitor or did you try re-initializing your subscriptions both via SQL Server Management Studio? – andrews Sep 06 '17 at 08:23
  • Sir, i have made a subscription> generate snapshot from publisher> re-initialized> start replication .... Now status is : when it start it looks fine after some downloading/uploading records it generate mentioned error. – Ahsan Mumtaz Abbasi Sep 06 '17 at 09:38
  • in the mean while there are 2 other subscription which are working perfectly . – Ahsan Mumtaz Abbasi Sep 06 '17 at 09:40
  • Please, convert the above comment to a self-posted answer. I'm sure it will be helpful for others in the future. – andrews Sep 11 '17 at 09:28

1 Answers1

1

Thanks for considering my question, Your response showing that you haven't faced this error in your environment. i just resolved mentioned issue by creating missing indexes on replication system tables like "Msmerge_content, Msmerge_tombstone, Msmerge_genhistory.....Msmerge....etc." after making missing indexes on replication system tables it run faster like a charm. so at conclusion if any one faces above mentioned error should take a look on missing indexes on replication system tables. Thank you. –

Ahsan Mumtaz Abbasi
  • 53
  • 1
  • 11