How to troubleshoot SQL Server synchronous replication

Original: SQL Server synchronous Replication Troubleshooting method

1. The row was not found at the subscriber when the copied command was applied

WORKAROUND: Use system stored procedures Sp_browsereplcmds ( returns the result set of the readable version copy command stored in the distribution database and uses it as a diagnostic tool.? This stored procedure executes on the distribution database at the Distributor )

Sp_browsereplcmds'xact_seqno_start]     ' xact_seqno_end  ]     ' originator_id]       'publisher_database_id]     '  article_id    ]],    @agent_id agent_id ]    , @compatibility_level Compatibility_level ]

Troubleshooting: At the Distributor-distribution db (distribution)

Use [distribution] go--query 1: Usual transaction sequence number, check command_id=1 execution statement exec sp_browsereplcmds @xact_seqno_start = ' 0x00044ed500008f3e00ab00000000 ', @xact_seqno_end = ' 0x00044ed500008f3e00ab00000000 '--Query 2: View the Synchronization object table (found in the result set above article _id/publication_id field value) Select *from msarticles where article_id=344--Query 3: Synchronization command Too much, view can specify @command_id[1] (must be transmitted @publisher _database_id[Query 2]) EXEC sp_browsereplcmds @xact_seqno_start = ' 0x00044ed500008f3e00ab00000000 ', @xact_seqno_end = ' 0x00044ed500008f3e00ab00000000 ', @publisher_database_id =4, @command_id =1--

--The 1th statement will appear in the following two scenarios (A/b)

--a, the row was not found on update

{call? [dbo] . [sp_msupd_dbo table name ] ? (.....)}

--b, deleted when the row was not found

{call? [dbo] . [sp_msdel_dbo table name ] ? (......}

Understanding Synchronization Stored Procedures:

The Subscriber synchronization interface format is as follows:

--Add a call to the stored procedure format: ((the synchronization store does not throw an error code when the subscription client is new) CREATE PROCEDURE [dbo]. [Sp_msins_dbotablename] @c1 tinyint, @c2 nvarchar, @c3 time, @c4 timeasbegin INSERT INTO [dbo]. [TableName] ([Id],[name],[starttime],[endtime]) VALUES (@c1, @c2, @c3, @c4) End go--Update call stored procedure format: (When a subscription client modifies data when no record is found; Error code: 20598) CREATE PROCEDURE [dbo]. [Sp_msupd_dbotablename] @c1 tinyint = null, @c2 nvarchar = null, @c3 time = null, @c4 time = null, @pkc1 tinyint = null, @bi TMap binary (1) asbegin Update [dbo]. [TableName] set[name] = case substring (@bitmap,) & 2 When 2 then @c2 else [Name] end,[starttime] = case substring (@ bitmap,1,1) & 4 When 4 then @c3 else [StartTime] end,[endtime] = case substring (@bitmap,) & 8 When 8 then @c4 E  LSE [EndTime] endwhere [ID] = @pkc1if @ @rowcount = 0 if @ @microsoftversion >0x07320000 exec sp_msreplraiserror 20598end go--Delete Call stored procedure format: (When the subscriber deletes data when no record is found; error code: 20598) CREATE PROCEDURE [dbo]. [Sp_msdel_dbotablename] @pkc1 tinyintasbegin Delete [dbo]. [TableName]where [ID] = @pkc1if @ @rowcount = 0 if @ @microsoftversion >0x07320000 exec sp_msreplraiserror 20598end G O

Workaround 1: (a, update,b, delete ): Add the missing rows to the Subscriber (add a record based on the error ID above) or use the Import Export method ( SQL statement synchronization specifies the above ID). (Cause: A missing data row on the subscriber causes the delete or update to not find the data row and error),

The following methods are not recommended (workaround 2, workaround 3)

Workaround 2: Change the synchronization of the stored procedure interface to throw out the wrong code, it will cause the data to be out of sync, if the cancellation is best to cancel only the stored procedure called when the deletion (such as: Sp_msdel_dbotablename)

Workaround 3: Use the SkipErrors parameter of the publishing agent to ignore the skip error 20598 (which causes the data to be out of sync, not be able to find the cause effectively, you need to compare the environment data (such as table: Number of records)) such as:

--c, error when new

{call? [dbo] . [sp_msins_dbo table ]?

Workaround 4: (C, error when new ): Error in new, workaround based on the hint may be the identity value or the primary health ID is not worth the data (delete the Subscriber) or the table structure

How to troubleshoot SQL Server synchronous replication