Friday, December 30, 2016

Querying the Data Validation Results

Typically, I use replication monitor to validate subscriptions and we can see the results on the subscription details window, as shown below. But if the publication has many tables, we can use a query instead, to easily spot the table that is out of synchronization.

We can run this query on the distribution database and provide the publisher database (e.g. WideWorldImporters) and the publication (e.g. TransactionTables)

from MSdistribution_history h with (nolock)
join MSdistribution_agents a with (nolock)
on h.agent_id =
where a.publisher_db = 'WideWorldImporters'
and a.publication = 'TransactionTables'
and h.comments like 'Table% out of synchronization%'