February 22, 2013 at 12:58 pm
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?
February 22, 2013 at 6:21 pm
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