Rows deleted in the subscriber - what to do? - SQL Server Transactional Replication

Question

Someone has delete by mistake a row of a table in the subscribed server/database.

I am getting the following error message:

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)

Now I have restored the deleted row in the replicated server, but the distributor to the subscriber agent is taking so long to apply the changes (currently nearly 1.5 hours).

How can I make it do this update immediately?

Answer 1

If you have mistakenly removed a row on the subscriber that should receive an update through replication, the easiest way to fix would be to re-construct the missing row on the subscriber that matches the row that is failing to update.

Once the row is on the subscriber, the update can run successfully (even if it just writes the same value into the desired column), then replication should continue happily.

I have had this same scenario occur in the past and replication continued without issue.

Answer 2

Here's the indicators of replication commands taking longer to apply to subscriber:

1. check the distribution agent job history. check for <stats state="2"

Article Level Parameterized Command Stats
*******************************************************************************

2017-12-28 00:01:47.952 <stats state="1" work="4274" idle="0"><reader fetch="12" wait="4274"/><writer write="4274" wait="11"/><sincelaststats elapsedtime="668" work="664" cmds="111" cmdspersec="0.000000"><reader fetch="3" wait="664"/><writer write="664" wait="3"/></sincelaststats><message>Normal events that describe both the reader and writer thread performance.</message></stats>
2017-12-28 00:02:49.529 9 transaction(s) with 9 command(s) were delivered.
2017-12-28 00:03:50.762 10 transaction(s) with 10 command(s) were delivered.
2017-12-28 00:05:00.082 13 transaction(s) with 12 command(s) were delivered.
2017-12-28 00:06:01.771 11 transaction(s) with 12 command(s) were delivered.
2017-12-28 00:06:47.991 <stats state="2" fetch="12" wait="4573" cmds="42123" callstogetreplcmds="9"><sincelaststats elapsedtime="300" fetch="0" wait="299" cmds="42123" cmdspersec="140.000000"/><message>Raised events that occur when an agent's reader thread waits longer than the agent's -messageinterval time. (By default, the time is 60 seconds.) If you notice State 2 events that are recorded for an agent, this indicates that the agent is taking a long time to write changes to the destination.</message></stats>
2017-12-28 00:07:03.974 10 transaction(s) with 9 command(s) were delivered.
2017-12-28 00:08:04.304 11 transaction(s) with 12 command(s) were delivered.
2017-12-28 00:09:15.350 12 transaction(s) with 11 command(s) were delivered.

2. Still on distribution agent job history. You can look at your stats on writer and reader thread of distribution agent. (eg.Time to Apply Cmds (ms) : 4752946)

************************ STATISTICS SINCE AGENT STARTED ***********************
12-28-2017 00:09:47

Total Run Time (ms) : 4773703   Total Work Time  : 4752946
Total Num Trans     : 770   Num Trans/Sec    : 0.16
Total Num Cmds      : 769   Num Cmds/Sec     : 0.16
Total Skipped Cmds  : 0 
Total Idle Time     : 0 

Writer Thread Stats
  Total Number of Retries   : 0 
  Time Spent on Exec        : 3211250 
  Time Spent on Commits (ms): 263488    Commits/Sec         : 0.16
  Time to Apply Cmds (ms)   : 4752946   Cmds/Sec            : 0.16
  Time Spent on Schema Changes (ms) : 0
  Time Cmd Queue Empty (ms) : 12694     Empty Q Waits > 10ms: 451
  Total Time Request Blk(ms): 12694 
  P2P Work Time (ms)        : 0     P2P Cmds Skipped    : 0

Reader Thread Stats
  Calls to Retrieve Cmds    : 9 
  Time to Retrieve Cmds (ms): 4556259   Cmds/Sec            : 0.17
  Time Cmd Queue Full (ms)  : 208913    Full Q Waits > 10ms : 648

So why are the commands are taking longer to apply in subscriber db?

We need to step back a bit and apply basic SQL Server troubleshooting on subscriber db as well. Check for blocking, IO contention, network issue, check for wait stats, triggers, cursors, long running job/transactions, service broker, AGs redo queue, etc. From my experience, the silent killer of replication performance is... triggers. You wouldn't be able to catch it unless you're using profiler or you understand how the data flow works. These could quickly stock up the pending commands to be delivered.

How can I make it do this update immediately?

Options (make sure to stop the distribution agent job before applying any changes):

1. Apply the missing command on the subscriber db and let the command flow to subscriber
2. Delete the command from distribution db (on MSrepl_commands table and make sure you specify xact_seqno and command_id). Apply the command manually in subsciber db.
3. Skip the missing command. (not recommended)
4. Change the -CommitBatchSize and -CommitBatchThreshold parameter to 1:1 just to see if the command flows properly to subscriber db. This is also good for troubleshooting to isolate the issue. Then set it back to default or your desired paramater.

I would highly recommend you to capture a baseline on replication commands stats (Distribution Agent). We can use Perfmon to monitor the following:

• Dist:Delivered Cmds/sec
• Dist:Delivered Trans/sec
• Dist:Delivery Latency

This will give us an idea on how long it will take to apply the commands in subscriber db. The before and after picture of commands stats is important as we can determine how slow is slow or what have changed since last baseline.

Answer 3

read this utility https://docs.microsoft.com/en-us/sql/tools/tablediff-utility?view=sql-server-2017

Create a backup table if the publisher has a filter on; to reduce fixing rows.

use tablediff.exe utility with -f

run fix output on the destination (subscriber DB)