Here is the scenario…
I received an alert today that one of the order tables (articles) on a subscriber would not allow NULL values. Well nothing was changed on the subscriber side so I had to figure out where the issue was.
Fortunately, the Order ID was logged in the error message so on the distribution server\database and all of the other fields for that record had NULL values. I wanted to remove the offending rows from replication so I ran the following on the distribution database: exec sp_browsereplcmds. This returns a result set in a readable version of the replicated commands stored in the distribution database, and is used as a diagnostic tool. From here you can grab the xact_seqno field from the result set and then you are able to remove the offending rows from the Distribution.dbo.msrepl_transactions and Distribution.dbo.msrepl_commands tables. Here are the steps:
Step 1 – Stop the SQL Server Agent on the Distribution Server this will stop all replication transactions from going into the distribution database.
Step 2 – Run the following T-SQL commands in the distribution database
use distribution
go
delete from msrepl_transactions where xact_seqno =
go
delete from msrepl_commands where xact_seqno =
go
Step 3 – Start the SQL Server Agent on the distribution server this will start all replication transactions to go into the distribution database.
Step 4 – If need be, restart the synchronization of the distribution agent on the distribution server. Replication should now continue.
No comments:
Post a Comment