March 25, 2010 at 11:44 am
I am at a loss here...I am working on setting up a new server migration (SQL 2000 to SQL 2005)
The new SQL 2005 server will replicate out to SQL 7 and SQL 2000 servers...
I got the replication set up on in our test environment to check on functionality and performance...
Things are all looking ok...but since this is test, everything is out of sync (understandable)...replication begins to have errors (duplicate rows / missing data at the subscriber)
SO...jump into the error log and replication monitor and I have to say I am disappointed on what little information you are given about what is causing the problem.
I am at a loss on how you find out either what the row(s) is that is causing duplicate row error OR how you find the row(s) that are missing on the subscriber...
I don't have the ability to run the re-initialization of the subscriber based on time and size of data.
When we have had issues on SQL 2000 generally the distribution log has shown at least the record / statement it is failing on and from there you can at least figure out what to do...
I can't even find any information outside of the error message in the title.
Does anyone have a quick and easy way to either query the tables to find the offending command or another recommendation?
Thanks in advance,
Lee
March 29, 2010 at 10:51 am
I know exactly what you mean when moving to SQL 2005 there is less info on the error. Ah but there is a way to find it, and actually when you get used to it you will like it better. It is a little complicated to explain but here is some info from my documents minus the pictures..
Troubleshooting Replication in Sql Server 2005 +
Viewing the Subscription below there is an error in the Distribution to the Subscribing server. From the GUI it is not exaclty clear what has caused the error other than the row was not found at the subscriber. You will need to obtain the Transaction Sequece number and command ID in the highlighted below.
There are several replication stored procedures but the one I find the most imporatnt is the SP_BROWSEREPLCMDS proc. Use it, learn to read it , It will be your best friend.
From the output below you will see the list of commands that are in the distribution database awaiting a push into the subscriber. Find the Transaction sequence number you copied from above as most importantly you will notice there could be multiple command ID for the same xact_seqno. So verify the command ID as well.
In this example the xact_seq_no and command_id shows the command {CALL [sp_MSdel_dboSETTING_VALUE] (557)}. It is trying to delete ID 557 from the SETTIGN_VALUE table. After looking at the data, it was determined the setting_value table was not in sync with the publisher and
***NOTE USE THE BELOW ONLY IF YOU DO NOT WANT THE TRANSACTION TO BE REPLICATED OVER!! This may not always be the case depending on what the error is.
so there is a command to remove the records that are being distributed. The records can be found in the select * from msrepl_commands table in the distribution database, you will need the xact_seq_no from previous findings although note that it add 5 trailing zeros to the actual command in the GUI. (dunno why)
use distribution
go
delete from msrepl_commands where xact_seqno= 0x001D8A3F000004920012 and command_id = 1
go
This will remove the command from trying to be distributed to the subscriber and should clear my error above. Let me know if you have any questions.
The msrepl_commands table does not show the actual command so you will neeed to go the the sp_browsereplcmds proc to find offending command.
Good luck,
Mike McNeer
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply