SQL Server updatable subscription Queue Reader Agent error: An attempted insert or update has failed

Original: SQL Server updatable subscription Queue Reader Agent error: An attempted insert or update has failed

Today it is found that the Queue Reader Agent keeps trying to start but always goes wrong:

The contents are as follows:

This may be caused by another problem that has just been dealt with (not mentioned here).

The queue reader is reading data from the subscription database and inserting data into the publication database. The above hint is the reason.

Because the publication database has 2 subscription sub-Libraries, it is not clear which data is caused by the number of subscriptions according to the library.

So execute the following code in 2 subscription databases to see which record is the same:

SELECT * from  [subdbname].[ DBO]. [Msrepl_queuedtraninfo] ORDER by maxorderkey ASC

Run a few times to view the output, if the first record remains unchanged, and the total number of records only increases, it means that the queue subscribed to the release is blocked in this record!

Workaround:

Delete this transaction from the queued transaction in this subscription library! However, the transaction is not synchronized back to the publication database.

SELECT * FROM [Platform]. [dbo]. [Msrepl_queuedtraninfo] WHERE tranid= ' 9xsm<<js713^ahxkjy5sq=5---0KG---' SELECT * from [Platform]. [dbo]. [MSreplication_queue] WHERE tranid= ' 9xsm<<js713^ahxkjy5sq=5---0KG---' DELETE from [Platform]. [dbo]. [Msrepl_queuedtraninfo] WHERE tranid= ' 9xsm<<js713^ahxkjy5sq=5---0KG---' DELETE from [Platform]. [dbo]. [MSreplication_queue] WHERE tranid= ' 9xsm<<js713^ahxkjy5sq=5---0KG---'

But specifically what statements or settings are caused by the " with CHECK OPTION", pending review.

SQL Server updatable subscription Queue Reader Agent error: An attempted insert or update has failed