replication

  • how do i check whether transactional replication is in sync?

    i checked in replication monitor tracer tokens are going thru.

    but is there any script to find out subscriber is in sync with publisher? all transactions are replicated?

  • Perhaps this will help:

    SELECT

    a.publisher_db,

    a.article,

    ds.article_id,

    ds.UndelivCmdsInDistDB,

    ds.DelivCmdsInDistDB,

    CASE WHEN md.[status] = 1 THEN 'Started'

    WHEN md.[status] = 2 THEN 'Succeeded'

    WHEN md.[status] = 3 THEN 'In Progress'

    WHEN md.[status] = 4 THEN 'Idle'

    WHEN md.[status] = 5 THEN 'Retrying'

    WHEN md.[status] = 6 THEN 'Failed'

    ELSE 'Other'

    END [Status],

    CASE WHEN md.warning = 0 THEN 'OK'

    WHEN md.warning = 1 THEN 'Expired'

    WHEN md.warning = 2 THEN 'Latency'

    ELSE 'OTHER'

    END [Warnings],

    md.cur_latency / 60.0 / 60.0 [Latency (min.)]

    FROM

    [YOURSERVER].Distribution.dbo.MSdistribution_status ds

    INNER JOIN [YOURSERVER].Distribution.dbo.MSarticles a

    ON a.article_id = ds.article_id

    INNER JOIN [YOURSERVER].Distribution.dbo.MSreplication_monitordata md

    ON md.agent_id = ds.agent_id

    WHERE

    UndelivCmdsInDistDB > 0

    AND a.publisher_db = 'YOURDATABASENAME'

    ORDER BY

    a.publisher_db,

    UndelivCmdsInDistDB DESC

    This will show you the number of undelivered commands in the distribution database that are still waiting to go to the subscriber. Remove the "UndelivCmdsInDistDB > 0" to show all your articles in the publication - so if the query above ("as-is") returns no rows, you're in sync.

    In a highly active OLTP environment, you will RARELY ever see this query return 0 rows...

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply