Sql-server – SQL Server 2005 merge replication failure

merge-replicationreplicationsql serversql-server-2005troubleshooting

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?

Best Answer

Just to complement or add to the other answer

there are times you get a really hairy message enter image description here

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).

enter image description here

enter image description here

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.

enter image description here

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

enter image description here

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.

Related Question