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>'
, @publisher_db = '<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 = '<Publisher Server>'
SET @PublicationDB = '<Publication DB>'
SET @SubscriberServer = '<Subscriber Server>'
SET @SubscriberDB = '<Subscriber DB>'
SET @PublicationName = '<Publication Name>'
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: