Fixing Missing Rows error in transactional replication

Published on September 5 2015 by LakshmiSaahul,Dhana Royal

Fixing Missing Rows error in transactional replication

Fixing Missing Rows error in transactional replication:

· Set up transactional replication

http://sqlserverreplicationtroubleshooting.blogspot.com/2011/04/setting-up-transactional-replication-in.html

· Once it is done.

o Delete one row of data from subscriber.

o Update the same row in publisher

Go to replication monitor to view the error:

Command attempted:

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

Same error you can view from following table

Select * from msrepl_errors

You can see that there are 2 items mentioned in error one is transaction sequence number:0x00000015000001B9000400000000 and another is command id:1

System Procedure sp_browsereplcmds takes the values transaction sequence number and command id as parameters and outputs the command that caused the errors.

Transaction sequence number:

Identifier for the transaction which contains commands.

Command ID:

Each command have a unique ID within the scope of the transaction sequence. The command starts at 1 and increment by 1 for each new command. Command should be updated in same sequence if it is more than one command.

Now How to fix the error:

· Get the article id and publisher database id from following query on distributor database using,

select *

from msrepl_commands (nolock)

where command_id = 1 and xact_seqno = 0x00000015000001B9000400000000

· Use the output of above query in following statemet on distributor DB,

sp_browsereplcmds @article_id = 1, @command_id = 1 , @xact_seqno_start= '0x00000015000001B9000400000000', @xact_seqno_end ='0x00000015000001B9000400000000',@publisher_database_id = 1

· Copy the command column from above statement output,

{CALL [dbo].[sp_MSupd_dboTestTable] (,N'testDelete',2,0x02)}

check for the sp_helptext on the proc

Create sql statement as mentioned in Procedure

SQL Server creates 3 procedure on subscriber which starts with sp_MS

[sp_MSdel_dboTestTable]

[sp_MSins_dboTestTable]

[sp_MSupd_dboTestTable]

· You can see from above statement that for ID – 2 update statement is failed

· Go to subscriber and insert the row for ID – 2

INSERT INTO [xyz].[dbo].[TestTable]

([id]

,[text])

VALUES

,'Anydata')

· Go and verify the replication monitor error is gone.

If there are mutiple rows are missing then follow the below steps:

· Get the MaxID of the Article on the subscriber from below :

select MAX(id) from TestTable

Get the missing ID from the procedure call ,Missing ID (in subscriber) and the MaxID if the article(in subscriber), Get the count

By connecting to the publisher Get the count

select COUNT(*) from TestTable(nolock) where id between 4 and 6 --3

Select 3 - 1 2 (Missing Rows Count)

· Create a temp table in the subscriber with same structure as the article(table being replicated)

select * INTO TestTable_ReplIssue__1 FROM TestTable(nolock) where 1=0

· Insert the rows into the temporary table by selecting the data from publisher using the linked server.

INSERT INTO TestTable_ReplIssue__1

select * FROM [MININT-8JHDRAI].TestDB.dbo.TestTable

where ID between 4 and 6

· Insert the missing rows into the article by comparing with the temp table created above.

BEGIN TRAN

sp_dropserver 'MININT-8JHDRAI', 'droplogins';

EXEC sp_addlinkedserver

'MININT-8JHDRAI',

N'SQL Server'

Advertising