June 25, 2012 at 2:50 am
Hi,
I am new to sql server replication. We are using SQL Server 2008 and its transactional replication. Replication is broken with below command:
{CALL [dbo].[sp_MSdel_dboMasterCodeSetVersions] (60)}
{CALL [dbo].[sp_MSdel_dboMasterCodeSetVersions] (59)}
By looking at the error, I can make out that some delete statment was run against the publisher which the system is trying to replicate but then the record is not found at the subscriber. If I insert the dummy record, its looking for my problem will be solved but I am not sure how to find the exact record in question. I used the below command to get the statement :
exec sp_browsereplcmds @xact_seqno_start='0x002F4FE7000377CC000D00000000' , @xact_seqno_end='0x002F4FE7000377CC000D00000000'
but all it does is giving me above {CALL .... command.
I have below questions on this:
1. how can I find the record in question so that i can insert the record at subscriber side so that replication can get that and delete that as expected and move smoothly with others waiting commands?
2. what (59) and (60) represents in above command?
3. In general, if I need to find out the record in question, how would I do that? I mean if a record inserted at publisher side which has already been inserted at the subscriber side OR a record is modified at publisher but already modified at subscriber OR record is deleted from publisher which is already deleted from subscriber.
My questions might sound silly but please guide me.
Thanks
June 25, 2012 at 4:18 am
When you build replication, the server creates stored procedures at the target database. The procedures have name that looks like this sp_MSoooXXXX. The ooo is replaced by the operation that it should run( UPD from update INS for insert and DEL for delete). The xxx part is replaced by the shema and table's name. In your case this is the procedure that deletes a record from dbo.MasterCodeSetVersions table. The number that is passed to this procedure is that table's primary key. You can check it by looking at the procedure's code. So to answer your 2 of your questions:
1)The missing record is the record that it's primary key has the value of 59 or 60 (or both). You can check it with select on that table.
2)The numbers represent the primary key of the record that should be deleted.
3)I don't really know.:-)
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 25, 2012 at 4:43 am
Thanks for the response Adi. I have found a work around for my problem but the solution is not full proof I mean it might create some data issue.
Work around might be usefule for others:
If the data integrity is not compromised or the data in question does not exist on either publisher and subscribers in case of DELETE operation and exist on all publisher and subscribers in case of INSERT/UPDATE then SP_SETSUBSCRIPTIONXACTSEQNO sp can be used to skip the blocking transaction. ex of this sp usage :
exec sp_setsubscriptionxactseqno
@publisher ='<server name>',
@publisher_db = '<publisher database>',
@publication ='<publication name>',
@xact_seqno = 0x002F4FE7000377CC000D -- exact seqence no of the blocking transaction.
In my case blocking transaction no was 0x002F4FE7000377CC000D00000000
Note : last eight 0's are removed as the same transaction had to be skipped.
in case of Insert/Update, if the transaction is missing from subscriber then insert them manually and let replication move on. If it still breaks then skip this transaction and you should be fine. Now How to find exact record is still a question to me but we certainly can use our common sense and use set operators or some tool to find the diff between the tables .
I hope this might be helpful to someone. Please feel free to let me know if my findings/observations/understanding is wrong.
-Onkar
June 25, 2012 at 6:45 am
You might want to have a look at the Agent Profiles.
You can create your own profile and add the error number to the -SkipErrors list in order to ignore your particular error.
June 25, 2012 at 8:36 am
This has been pretty well answered but wanted to add one point on the delete procedures. You can actually modify those and comment out the error handling. That will allow those delete statements that can't find a corresponding row on the subscriber to go through without generating errors. This will also not cause any inconsistency errors as you were going to delete the data anyway. Setting the agent profile as Ian stated is another solution but with that configuration you will not get errors for updates or inserts that have issues and will ultimately have data consistency issues.
Let me know if you need me to clarify my statements in any way.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
June 26, 2012 at 1:19 am
Thanks for the response Ian/David. I understand "delete" operation will not lead to any data inconsistency. As I am new to SQL Server, I am not very sure how to create agent profile. COuld you please help me understand that part?
David: could you please elaborate your point a little bit?
June 26, 2012 at 1:31 am
It probably a good idea for you to have a look at the topic in books online first, then post back with any specific questions.
http://msdn.microsoft.com/en-us/library/ms151223(v=sql.90).aspx
June 26, 2012 at 2:49 pm
Sorry for the slow reply on this. To explain further, if you look in the delete stored procedure on the subscriber (refer to previous poster information on procedure naming convention) in the subscription database you will see the following;
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
That whole section can be commented out as that is really only looking for a "row not found" when trying to delete that data.
Also, if you note, the error, 20598, is referenced in the BOL topic regarding replication agent profiles, and specifically under the -SkipErrors option. So, if you were to change the profile you would be skipping more than just the 20598 error. Commenting out the code above in the stored procedure will only ignore errors in the delete procedure.
Hope that helps a bit.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply