Why am I unable to skip a transaction in SQL Server 2017 transactional replication using the sp_setsubscriptionxactseqno stored procedure?

Question

I have a transaction number of a transaction that is throwing the following error:

Command attempted: if @@trancount > 0 rollback tran 
(Transaction sequence number: 0x00036322000B2628000100000000, Command ID: 1)

Error messages: The row was not found at the Subscriber when applying the replicated 
(null) command for Table '(null)' with Primary Key(s): (null) 
(Source: MSSQLServer, Error number: 20598) Get help: http://help/20598 The row was not found at 
the Subscriber when applying the replicated (null) command for Table '(null)' 
with Primary Key(s): (null) (Source: MSSQLServer, Error number: 20598) 
Get help: http://help/20598

When I execute the following:

sp_setsubscriptionxactseqno@publisher = ''  
        ,@publisher_db = ''  
        , @publication = 'ALL'  
        , @xact_seqno = 0x00036322000B2628000100000000

in order to skip the offending transaction and then try to sync up the subscription, it doesn't skip the transaction, instead it keeps throwing the same error.

When I execute the following:


USE distribution 
GO

DECLARE @PublisherServer  VARCHAR(50), 
        @PublicationDB    VARCHAR(50), 
        @SubscriberServer VARCHAR(50), 
        @SubscriberDB     VARCHAR(50), 
        @PublicationName  VARCHAR(50) 

SET @PublisherServer = '' 
SET @PublicationDB = '' 
SET @SubscriberServer = '' 
SET @SubscriberDB = '' 
SET @PublicationName = '' 

EXEC Sp_helpsubscriptionerrors 
  @PublisherServer, 
  @PublicationDB, 
  @PublicationName, 
  @SubscriberServer, 
  @SubscriberDB

I get 1,860 rows back all being:

id  time    error_type_id   source_type_id  source_name error_code  error_text  xact_seqno  command_id  session_id
23326   2020-07-15 15:52:27.640 0   0   MSSQL_ENG   20598   The row was not found at the Subscriber when applying the replicated (null) command for Table '(null)' with Primary Key(s): (null)  0x00036322000B2628000100000000  1   0
23326   2020-07-15 15:52:27.640 0   1   MSSQL_ENG       if @@trancount > 0 rollback tran    0x00036322000B2628000100000000  1   0
23326   2020-07-15 15:52:27.640 0   0   MSSQL_ENG   20598   The row was not found at the Subscriber when applying the replicated (null) command for Table '(null)' with Primary Key(s): (null)  0x00036322000B2628000100000000  1   0
23322   2020-07-15 15:50:09.100 0   1   MSSQL_ENG       if @@trancount > 0 rollback tran    0x00036322000B2628000100000000  1   0
23322   2020-07-15 15:50:09.100 0   0   MSSQL_ENG   20598   The row was not found at the Subscriber when applying the replicated (null) command for Table '(null)' with Primary Key(s): (null)  0x00036322000B2628000100000000  1   0
23322   2020-07-15 15:50:09.083 0   0   MSSQL_ENG   20598   The row was not found at the Subscriber when applying the replicated (null) command for Table '(null)' with Primary Key(s): (null)  0x00036322000B2628000100000000  1   0
23318   2020-07-15 15:47:45.423 0   0   MSSQL_ENG   20598   The row was not found at the Subscriber when applying the replicated (null) command for Table '(null)' with Primary Key(s): (null)  0x00036322000B2628000100000000  1   0

Why am I unable to skip that transaction? What am I missing?

EDIT:

I have tried to re-initialize the subscription, but it won't re-initialize.
I manually attempt to start the 'Snapshot Agent' job from the 'Agents' tab and receive the following:

Status: Completed
Job: Snapshot Agent
Last Start Time: 7/16/2020 7:07:43 AM
Duration: 00:00:01
Last Action: [0%] A snapshot was not generated because no subscriptions needed initialization.

I marked two subscriptions for re-initialization and received the above.
Below is a screenshot of the subscription listing:

But, even when I try to manually start the snapshot job, I receive the following:

I don't understand what's going on. What am I doing wrong or missing?

Answer 1

Hi Wayne,

>>>Error messages: The row was not found at the Subscriber when applying the replicated command.

Check following posts if help:
https://dba.stackexchange.com/questions/158973/rows-deleted-in-the-subscriber-what-to-do-sql-server-transactional-replicat
https://support.microsoft.com/en-us/help/3066750/how-to-troubleshoot-error-20598-the-row-was-not-found-at-the-subscribe
https://repltalk.com/2019/05/28/how-to-skip-a-transaction/

Best Regards,
Cris

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

Hi Wayne,

>>>Error messages: The row was not found at the Subscriber when applying the replicated command.

Check following posts if help:
https://dba.stackexchange.com/questions/158973/rows-deleted-in-the-subscriber-what-to-do-sql-server-transactional-replicat
https://support.microsoft.com/en-us/help/3066750/how-to-troubleshoot-error-20598-the-row-was-not-found-at-the-subscribe
https://repltalk.com/2019/05/28/how-to-skip-a-transaction/

Best Regards,
Cris

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

I have tried all of those different things and still nothing. I don't understand this. Why is MS unable to help me? I think part of what you're missing is the fact that all 3 values that are being returned in this error are NULL, the command, table and primary key. How in blazes am I supposed to get anything out of that error?

Answer 3

Hi Wayne,

Please try to reinitialize the Subscriptions(Use a new snapshot) .

All 3 values that are being returned  are NULL  in this error 20598 message, which may be due to the low SQL Server version of the subscriber server. try to use the code in this post to get more information.

Best Regards,
Cris

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].