SQL Server 2005 merge replication failure

Question

We have a customer who, when trying to sync a client (subscriber) to the server (publisher,) receives the following error:

2012-11-30 21:03:33.334 Percent Complete: 18
2012-11-30 21:03:33.334 Data validation failed for one or more articles. When troubleshooting, check the output log files for any errors that may be preventing data from being synchronized properly. Note that when error compensation or delete tracking functionalities are disabled for an article, non-convergence can occur.
2012-11-30 21:03:33.334 OLE DB Distributor 'EnvisionSQL\SQL2005': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2012-11-30 21:03:33.506 Percent Complete: 0
2012-11-30 21:03:33.506 Category:NULL
Source:  Merge Process
Number:  -2147200953
Message: Data validation failed for one or more articles. When troubleshooting, check the output log files for any errors that may be preventing data from being synchronized properly. Note that when error compensation or delete tracking functionalities are disabled for an article, non-convergence can occur.
2012-11-30 21:03:33.521 Percent Complete: 0
2012-11-30 21:03:33.521 Category:NULL
Source:  Merge Process
Number:  -2147200953 
Message: Article 'TB_CORE_DAILY' failed data validation (rowcount only). Rowcount actual: 80367, expected: 0.
2012-11-30 21:03:33.537 Percent Complete: 0
2012-11-30 21:03:33.537 Category:NULL
Source:  Merge Process
Number:  -2147200953
Message: Article 'TB_CORE_INSPECTION_VIOL' failed data validation (rowcount only). Rowcount actual: 19451, expected: 0.

The Server is 2005 and the local database engine is 2005 express.

What could be causing the mismatch of actual/expected row counts?

---

I'm trying to do more research on this so I thought I would re-ignite this discussion to see if anyone else has some input.

I understand how rowcount validation works, but why would I receive an expected value of 0 on a table that has known data?

Answer 1

Just to complement or add to the other answer

there are times you get a really hairy message

first thing I do is to turn on the verbose:

Turn on verbose history (Replication monitor->Agent Profile->Verbose History) and use the -output parameter and select a destination for the output file (Subscriber Replication Job -> Edit Run Agent Job -> In the command window add -output C:\MyFilePath\FileName.log). 

and:

Have a look at the latest part of the command below, you see the output parameter.

-Publisher [TCP-ODS01] -PublisherDB [concord_ods] -Publication [MERLIN_PUBLICATION] -Subscriber [MERLIN771] -SubscriberDB [ConCORD_ODS]   -Distributor [TCP-MERGE01] -DistributorSecurityMode 1 -HostName [MERLIN771]  -output C:\Replication\MERLIN771_1132.log

Now we can have a look at the log file.

Now you can have a look on the log generated and proceed from there.   Example (see the next picture below):

The command on the job is:

--This is the MERLIN307 subscriber, I am troubleshooting, outputting to the log at c:\replication and using the - VerbatimTextObjectScripting 0 option.
-Publisher [TCP-ODS01] -PublisherDB [concord_ods] -Distributor [TCP-MERGE01] -Publication [MERLIN_PUBLICATION] -ReplicationType 2 -DistributorSecurityMode 1  -DynamicFilterHostName [MERLIN307] -DynamicSnapshotLocation [\\TCP-MERGE01\REPLDATA\\unc\TCP-ODS01_CONCORD_ODS_MERLIN_PUBLICATION\MERLIN307_9\] -PartitionId 9  -VerbatimTextObjectScripting 0 -output C:\Replication\MERLIN307-983.log

I can't really give you a straight answer to your case in particular, but I can definitely help you finding out what the problem is, using these steps above.

If it sounds confusing, drop a comment below I will try to be more specific.

Answer 2

Check if you have any conflicts between the subscriber and the publisher and try to resolve these conflicts.

Answer 3

I can see on your error messages that data validation could not be done, although as you said the publisher and the subscriber tables have the exact same rowcount.

I don't think it would be a conflict, though you could check for that.

for any errors related to could not be verified on merge replication in sql 2005 something like this:

Error messages: • The subscription to publication 'MERLIN_PUBLICATION' could not be verified. Ensure that all Merge Agent command line parameters are specified correctly and that the subscription is correctly configured. If the Publisher no longer has information about this subscription, drop and recreate the subscription. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201019) Get help: http://help/MSSQL_REPL-2147201019 Invalid object name 'dbo.sysmergepublications'. (Source: MSSQLServer, Error number: 208) Get help: http://help/208

It all depends on how you got your merge replication set up. for instance, if you have all diced up, meaning that each subscribe gets only its own share, and for checking the whole thing they need to connect to the main server, then running a snapshot is not a big deal most of the time.

if, however, everybody has everything on their machines, then, snapshot running can take a while. I try not to come to that, whenever possible.

there are a few things I could say, probably I would have to post more than one answer.

In the merge replication, all the things you need to see are inside the databases themselves.

whatever you do, backup all the tables before you do it.

for example:

select * into MSMerge_Replinfo_20130116 from MSMerge_Replinfo

lets say I have a server called ODS and all my subscribers are named MERLIN SOMETHING my database is called concord_ord

have a look at this:

-- #### ODS

use concord_ods
go

select top 10 
recgen,recguid,sentgen,sentguid,pubid,subid,replnickname 
 from sysmergesubscriptions with (nolock)
where subscriber_server = 'MERLIN026'


-- ODS the original after the reinitialisation
-- ODS recgen   recguid                                 sentgen sentguid    
-- ODS 71374    7D24ABFE-DC96-4C7C-BCC7-F48830F82493    6336565 7D24ABFE-DC96-4C7C-BCC7-F48830F82493    


-- ODS - I modified but it did not work
-- ODS recgen   recguid                                 sentgen sentguid                                pubid                                   subid                                   replnickname
-- ODS 5933523  1530D5D0-61E9-4000-9BD5-F01F3D08601A    6278697 1F0CBCD4-B959-44C9-B120-77FC09EC5082    8E72F963-2C85-4005-BC5F-8494A1BE23BE    9F26E61E-AF33-4F83-95E6-32AD98155048    0xAF339F26E61E


-- ### merlin026

--merlin026 bef.rei. recgen  recguid                                sentgen sentguid
--merlin026 bef.rei. 6278697 1F0CBCD4-B959-44C9-B120-77FC09EC5082   5933523 1530D5D0-61E9-4000-9BD5-F01F3D08601A

--merlin026 aft.rei. recgen  recguid                                sentgen sentguid
--merlin026 aft.rei. 6454923 621E577A-80D3-4177-AFF1-A226F912E779   6454674 16D59CEE-9191-44E5-B8A2-C6C45570A004

then go to your subscribers and check the data there: for example:

use concord_ods
go


select top 10 
recgen,recguid,sentgen,sentguid,pubid,subid,replnickname 
 from sysmergesubscriptions with (nolock)
where subscriber_server = 'TCP-ODS01'

-- ODS - I modified but it did not work
-- ODS recgen   recguid                                 sentgen sentguid                                pubid                                   subid                                   replnickname
-- ODS 5933523  1530D5D0-61E9-4000-9BD5-F01F3D08601A    6278697 1F0CBCD4-B959-44C9-B120-77FC09EC5082    8E72F963-2C85-4005-BC5F-8494A1BE23BE    9F26E61E-AF33-4F83-95E6-32AD98155048    0xAF339F26E61E


-- 026 recgen   recguid                                 sentgen sentguid                                pubid                                   subid                                   replnickname
-- 026 6278697  1F0CBCD4-B959-44C9-B120-77FC09EC5082    5933523 1530D5D0-61E9-4000-9BD5-F01F3D08601A    8E72F963-2C85-4005-BC5F-8494A1BE23BE    8E72F963-2C85-4005-BC5F-8494A1BE23BE    0x2C858E72F963

-- After the reinitialisation
-- 026 recgen   recguid                                 sentgen sentguid                                pubid                                   subid                                   replnickname
-- 026 6454923  621E577A-80D3-4177-AFF1-A226F912E779    6454674 16D59CEE-9191-44E5-B8A2-C6C45570A004    8E72F963-2C85-4005-BC5F-8494A1BE23BE    8E72F963-2C85-4005-BC5F-8494A1BE23BE    0x2C858E72F963


select top 10 
subscriber_server ,recgen,recguid,sentgen,sentguid,pubid,subid,replnickname 
 from sysmergesubscriptions with (nolock)

--on MERLIN026
--three subscriptions?
--subid:
--85A04E38-78AA-41F6-9956-3538656F0912
--A27948E7-54C3-4EBB-9D3F-DFF46D723313
--534EFA1C-3CE7-4C93-9943-F580CD7364F4

-- none of these match to ODS subid:
--9F26E61E-AF33-4F83-95E6-32AD98155048

-- hypothesis: mismatched subid is causing synchronisation failure

-- proposed solution: update to subid to bring it in line with ODS subid
-- this should allow the two databases to connect

-- check to see where the subid appears in the database
select * from sys.columns
where name = 'subid'

select * from sys.tables
where object_id in (
    select object_id from sys.columns
    where name = 'subid'
    )


-- check these tables for data    
select * from MSmerge_identity_range -- 51 rows
select top 10 * from MSmerge_altsyncpartners -- 0
select top 10 * from MSmerge_supportability_settings -- 0
select * from MSmerge_sessions -- 10 rows
select top 10 * from MSmerge_log_files -- 0


-- keep data, just in case it all goes wrong and we need to roll back the change
select * into  dbo.MSmerge_sessions_20130116 from MSmerge_sessions 

select * from MSmerge_sessions 

--I spotted that over 8 records had a different subid, and the rest 
--rest of the records had the correct subid, therefore I deleted those
--which had a wrong subid
delete from MSmerge_sessions where subid <> '9F26E61E-AF33-4F83-95E6-32AD98155048'

-- we also need to align the replnickname
-- so make sure this isn't used anywhere else

select * from sys.tables
where object_id in (
    select object_id from sys.columns
    where name = 'replnickname'
    )
-- just in sysmergesubscriptions

-- the subid is repid in MSMerge_Replinfo
select * from MSMerge_Replinfo

-- I found 2 rows on MSMerge_Replinfo
-- where the repid is not '9F26E61E-AF33-4F83-95E6-32AD98155048'
-- I will remove them

select * into MSMerge_Replinfo_20130116 from MSMerge_Replinfo

select * from MSMerge_Replinfo

delete from MSMerge_Replinfo
where repid <> '9F26E61E-AF33-4F83-95E6-32AD98155048'


-- make sure this doesn't appear anywhere else
select * from sys.tables
where object_id in (
    select object_id from sys.columns
    where name = 'repid'
    )
-- just in MSMerge_Replinfo


    select status, replnickname from sysmergesubscriptions
    where subid = '8E72F963-2C85-4005-BC5F-8494A1BE23BE'

-- use a transaction so that we can make further checks post-update
begin transaction
select @@trancount

    -- update tables
    update sysmergesubscriptions
    set subid = '9F26E61E-AF33-4F83-95E6-32AD98155048'
        , status = 1
        , replnickname = 0xAF339F26E61E
    where subid = '8E72F963-2C85-4005-BC5F-8494A1BE23BE'

    update  MSMerge_Replinfo
    set repid = '9F26E61E-AF33-4F83-95E6-32AD98155048'
    where repid = '85A04E38-78AA-41F6-9956-3538656F0912'

    update MSmerge_sessions 
    set subid = '9F26E61E-AF33-4F83-95E6-32AD98155048'

    update MSmerge_identity_range 
    set subid = '9F26E61E-AF33-4F83-95E6-32AD98155048'

    -- check result
    select * from sysmergesubscriptions with (readuncommitted)
    select * from MSMerge_Replinfo with (readuncommitted)
    select * from MSmerge_sessions with (readuncommitted)
    select * from MSmerge_identity_range with (readuncommitted)

-- result is good, commit
--rollback transaction
commit transaction
select @@trancount -- 0



select subscriber_server,pubid, subid,replnickname,recgen,sentgen,recguid,sentguid 
from sysmergesubscriptions with (readuncommitted)
where subscriber_server = 'MERLIN026'
   or subscriber_server = 'MERLIN864'


--merlin026 - not working
--subscriber_server pubid                                   subid                                   replnickname    recgen  sentgen recguid                                 sentguid
--TCP-ODS01         8E72F963-2C85-4005-BC5F-8494A1BE23BE    9F26E61E-AF33-4F83-95E6-32AD98155048    0xAF339F26E61E  6278697 5933523 1F0CBCD4-B959-44C9-B120-77FC09EC5082    1530D5D0-61E9-4000-9BD5-F01F3D08601A
--MERLIN026         8E72F963-2C85-4005-BC5F-8494A1BE23BE    85A04E38-78AA-41F6-9956-3538656F0912    0xBD244C8F11E7  NULL    NULL    NULL                                    NULL
--MERLIN026         2F0867E6-4580-4DB9-9405-9C836413B7D2    A27948E7-54C3-4EBB-9D3F-DFF46D723313    0x78AA85A04E38  NULL    NULL    NULL                                    NULL
--MERLIN026 7       C2C1785-0FE1-4717-9E53-BAAFD889B89A     534EFA1C-3CE7-4C93-9943-F580CD7364F4    0xBD244C8F11E7  NULL    NULL    NULL                                    NULL



--merlin864 - working fine
--sysmergesubscriptions 
--subscriber_server pubid                                   subid                                   replnickname    recgen  sentgen recguid                                 sentguid
--TCP-ODS01         8E72F963-2C85-4005-BC5F-8494A1BE23BE    8E72F963-2C85-4005-BC5F-8494A1BE23BE    0x2C858E72F963  6456716 2857356 18607229-F77A-4768-89D3-DEF82775340A    6A93A5F5-AC77-4A96-9472-4B64EAFF4435
--MERLIN864         8E72F963-2C85-4005-BC5F-8494A1BE23BE    776E13B8-6997-4ABD-9F55-C277EF86A3B0    0xEFBB4CCC71C0  NULL    NULL    NULL                                    NULL

-- ODS
--subscriber_server pubid                                   subid                                   replnickname    recgen  sentgen recguid                                 sentguid
--MERLIN026         8E72F963-2C85-4005-BC5F-8494A1BE23BE    9F26E61E-AF33-4F83-95E6-32AD98155048    0xAF339F26E61E  5933523 6278697 1530D5D0-61E9-4000-9BD5-F01F3D08601A    1F0CBCD4-B959-44C9-B120-77FC09EC5082
--MERLIN864         8E72F963-2C85-4005-BC5F-8494A1BE23BE    776E13B8-6997-4ABD-9F55-C277EF86A3B0    0xEFBB4CCC71C0  2857356 6456716 6A93A5F5-AC77-4A96-9472-4B64EAFF4435    18607229-F77A-4768-89D3-DEF82775340A

--merlin026 - after the reinitialisation
--subscriber_server pubid                                   subid                                   replnickname    recgen  sentgen recguid sentguid
--TCP-ODS01         8E72F963-2C85-4005-BC5F-8494A1BE23BE    8E72F963-2C85-4005-BC5F-8494A1BE23BE    0x2C858E72F963  6454923 6454674 621E577A-80D3-4177-AFF1-A226F912E779    16D59CEE-9191-44E5-B8A2-C6C45570A004
--MERLIN026         8E72F963-2C85-4005-BC5F-8494A1BE23BE    9F26E61E-AF33-4F83-95E6-32AD98155048    0xAF339F26E61E  NULL    NULL    NULL                                    NULL


-- MERLIN026 after update below
--subscriber_server pubid                                   subid                                   replnickname    recgen  sentgen recguid                                 sentguid
--TCP-ODS01         8E72F963-2C85-4005-BC5F-8494A1BE23BE    8E72F963-2C85-4005-BC5F-8494A1BE23BE    0x2C858E72F963  6278697 5933523 1F0CBCD4-B959-44C9-B120-77FC09EC5082    1530D5D0-61E9-4000-9BD5-F01F3D08601A
--MERLIN026         8E72F963-2C85-4005-BC5F-8494A1BE23BE    9F26E61E-AF33-4F83-95E6-32AD98155048    0xAF339F26E61E  NULL    NULL    NULL                                    NULL

--MERLIN026         2F0867E6-4580-4DB9-9405-9C836413B7D2    A27948E7-54C3-4EBB-9D3F-DFF46D723313    0x78AA85A04E38  NULL    NULL    NULL                                    NULL
--MERLIN026         7C2C1785-0FE1-4717-9E53-BAAFD889B89A    534EFA1C-3CE7-4C93-9943-F580CD7364F4    0xBD244C8F11E7  NULL    NULL    NULL                                    NULL

-- ## MERLIN026
use concord_ods
go

select @@servername

begin tran T01

    update sysmergesubscriptions 
    set subid = '8E72F963-2C85-4005-BC5F-8494A1BE23BE'
      ,replnickname = 0x2C858E72F963
    where subscriber_server = 'TCP-ODS01'

    update sysmergesubscriptions 
    set subid = '9F26E61E-AF33-4F83-95E6-32AD98155048'
      ,replnickname = 0xAF339F26E61E
    where subscriber_server = 'MERLIN026'
      and subid = '85A04E38-78AA-41F6-9956-3538656F0912'

    -- make sure that the status is 1 otherwise it does not verify
    update sysmergesubscriptions 
    set status = 1
    where subscriber_server = 'MERLIN026'
      and subid = '9F26E61E-AF33-4F83-95E6-32AD98155048'


    select subscriber_server,pubid, subid,replnickname,recgen,sentgen,recguid,sentguid 
    from sysmergesubscriptions with (readuncommitted)


--rollback tran T01
--commit tran T01

select @@trancount

I know it is a lot to take on. But it is worth it, if you want to avoid running snaps.

In this case above, one server got fixed, and one needed to re-subscribe.