Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have transactional replication with many publishers(50+) and one central subscriber(data collecting). Is there some way(feature of SQL server, 3th party tool) how to monitor all publications from one point ideally from central subscriber?(something like monitoring server with multiple publications with Replication monitor)


share|improve this question

Yes there is. The way I do it is I have a SQL Agent job post tracer tokens at a regular interval to the publishers and then monitor their progress from the distributor (where their history is stored). I created a view in the distribution database that helps with that.

USE [distribution]

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[tokens]'))
    DROP VIEW [dbo].[tokens]


create view [dbo].[tokens] as
select as [publisher],
    p.publication, as [subscriber],
    datediff(second, t.publisher_commit, t.distributor_commit) as [pub to dist (s)],
    datediff(second, t.distributor_commit ,h.subscriber_commit) as [dist to sub (s)],
    datediff(second, t.publisher_commit, h.subscriber_commit) as [total latency (s)]
from mstracer_tokens t
inner join MStracer_history h
    on t.tracer_id = h.parent_tracer_id
inner join mspublications p
    on p.publication_id = t.publication_id
inner join sys.servers ps
    on p.publisher_id = ps.server_id
inner join msdistribution_agents da
    on h.agent_id =
inner join sys.servers ss
    on da.subscriber_id = ss.server_id

I leave it as an exercise to the reader to turn that into monitoring. I'd give you what I have, but recently realized that it has a bug in it. But things to keep in mind:

  • A given publisher may not have had any tokens posted in the time frame that you're looking for. That should be flagged
  • As a measure of latency, you want the publisher_commit time for the most recent token to commit to the subscriber

Good luck!

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.