Record the troubleshooting process of SQL Server database subscription and publishing

Keywords: SQL Server Database SQL Stored Procedure

Record the error reporting and solution process in the process of SQL server subscription and publishing

Report errors:

20598 error reporting
20598 error reporting is mainly caused by the data inconsistency between the databases at both ends. The database generates an error reporting prompt when applying the stored procedure

The row was not found at the subscriber when the replicated command was applied
Error message
Attempted command:

if @@trancount > 0 rollback tran

Transaction serial number:

--0x0000FDAB0003A908008F00000000 command ID: 7

Error message:

The row was not found at the subscriber when the replicated command was applied (source: MSSQLServer, error number: 20598)

Get help: http://help/20598

The row was not found at the subscriber when the replicated command was applied (source: MSSQLServer, error number: 20598)

Get help: http://help/20598

Execute the following statement on the publication database to view the id of the error transaction

use distribution
go
exec sp_helpsubscriptionerrors 'SSDTCEMANTEST','ssdt','skl_11_8','ANCHNET','ssdt';
use distribution
go
select * from
dbo.MSarticles m
where exists (select mc.article_id from MSrepl_commands mc where mc.xact_seqno=0x00011F4000009D42006B00000000 AND mc.article_id = m.article_id )

EXEC Sp_browsereplcmds
@xact_seqno_start='0x00011F4000009D42006B00000000',
@xact_seqno_end='0x00011F4000009D42006B00000000'

According to the actual situation, recover the data or skip the stuck transaction ID

---------------Skip error on subscription machine------------------------
-----------------------On the distribution machine--------------------------
--grammar
exec sp_helpsubscriptionerrors [ @publisher = ]'publisher'
, [ @publisher_db = ]'publisher_db' 
, [ @publication = ]'publication' 
, [ @subscriber = ]'subscriber' 
, [ @subscriber_db = ]'subscriber_db'

--get publisher subscriber
select*from MSsubscriber_info

--get publisher_db publication subscriber_db=publisher_db
select*from MSpublications

--example
exec sp_helpsubscriptionerrors 'SQLw2k8','dbtranpub','dbtranpub_pub','SQLw2k8Subscriber','dbtransub'

--Obtain xact_seqno value

----------------------On the subscription machine---------------------------
sp_setsubscriptionxactseqno [ @publisher= ]'publisher', 
[ @publisher_db= ]'publisher_db', 
[ @publication= ]'publication', 
[ @xact_seqno= ] xact_seqno

--example(Ignore)
exec sp_helpsubscriptionerrors 'SQLw2k8','dbtranpub','dbtranpub_pub',xact_seqno;

Posted by mike_revolution on Tue, 12 Nov 2019 13:02:05 -0800