Script to Identify out of sync tables in Replication

Below script will return out of sync tables after a Subscription validation has been performed
— Set the isolation level to prevent any blocking/locking
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT
mda.publication [PublicationName],
mdh.start_time [SessionStartTime],
mdh.comments [Comments]

FROM distribution.dbo.MSdistribution_agents mda
JOIN distribution.dbo.MSdistribution_history mdh ON mdh.agent_id = mda.id

— Update Publication name as appropriate
WHERE mda.publication = ‘My Publication’
AND mdh.comments LIKE ‘%might be out of%’
— This next line restricts results to the past 24 hours.
AND mdh.start_time > (GETDATE() – 1)
— Alternatively, you could specify a specific date/time: AND mdh.start_time > ‘2012-04-25 10:30’
— View most recent results first
ORDER BY mdh.start_time DESC

The Comments column will contain the following message if a table is out of sync:
Table ‘MyTable’ might be out of synchronization. Rowcounts (actual: %value, expected: %value). Checksum values (actual: -%value, expected: -%value).
Make a list of all tables that are returned by the aforementioned script.

Now the determination needs to be made as to the level of impact.

The Reinitialize All Subscriptions option should be used if the following is true:

Large number of tables affected (majority of published tables)
Unaffected tables are small in size (if the snapshot for the unaffected tables is going to be very small, it’s much easier to just reinitialize everything)

Dropping and re-adding individual tables should be used if the following is true:

The number of tables affected is far less than the total number of tables
The tables that are unaffected are very large in size and will cause significant latency when pushing the snapshot
The latter was the case in my scenario (about 200 out of 1,000 tables were out of sync, and the ~800 tables that were in sync included some very large tables).

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.