Comments (0)

  1. Code returns zero data!

  2. Code returns nothing!

    1. Can you provide some details of how you are running it?

  3. I changed the 2 parameters, and executed using the distribution database. Additional comments in your code would be very helpful. I am running on sql 2008 and 2012. 35 instances total, and multiple transactional replications.

  4. I’ve been meaning to revisit this query for a long time to make it usable for more than just transactional replication and the distribution agent. I will comment it better when I do that.

  5. sanjaykumar.rajarao

    code returned no data.

    — Set Publisher server name
    Set @Publisher = select @@servername;
    — Set Publisher database name
    Set @PublisherDB = ‘distribution’;

    1. Are you sure that the Publisher DB is named distribution? That’s generally the name of the distributor DB, not the publisher DB.

  6. Running on 2008 R2.
    Confirmed both servername and dbname is correct.
    I know current latency is few hours (it is on purpose, we call it managed replication).
    But shows no data and getting this error:
    Msg 537, Level 16, State 3, Line 12
    Invalid length parameter passed to the LEFT or SUBSTRING function.

    1. Can you tell me what the values in the agent_name column in dbo.MSreplication_monitordata is?

      1. I am sorry those names have servername that I cannot send because comments publish automatically in on your blog.
        I can send to you on your personal email if you send me.
        I think the problem is here:Len(MD.publication)
        Publication name is not included in the agent name where agent type is 3.

  7. I think your code is written for individual agent (one distribution agent for each publication). We use shared agent. One distribution agent per publisher and subscriber.

    1. Yes. I was sharing a script I wrote for a specific situation. Was never meant to be a fully generic script that would work for all environments. It’s just not possible to think of every possible scenario and test it.

  8. So I modified the code to give the correct subscriber name still no record.
    SubString(MD.agent_name,
    Len(MD.publisher) + Len(MD.publisher_db) + 3,
    Charindex(‘-‘, MD.agent_name,
    Len(MD.publisher) + Len(MD.publisher_db) + 4) –
    (Len(MD.publisher) + Len(MD.publisher_db) + 3))

  9. Thanks for the script. I was having the same error “Invalid length parameter passed to the LEFT or SUBSTRING function”. I believe it happened because my server names have dashes “-” and so my publication names. The names of my publications are big so they also get truncated in the “agent_name” column of MSreplication_monitordata. Below are some suggested changes to better find the subscriber server. I am also displaying the publication name since I have multiple publication for the same database.

    Declare @Publisher sysname,
    @PublisherDB sysname;

    — Set Publisher server name
    Set @Publisher = ‘Publisher server name’;
    — Set Publisher database name
    Set @PublisherDB = ‘Publisher database name’;

    — Refresh replication monitor data
    Exec sys.sp_replmonitorrefreshjob @iterations = 1;

    With MaxXact (ServerName, PublisherDBID, XactSeqNo)
    As (Select S.name, DA.publisher_database_id, max(H.xact_seqno)
    From dbo.MSdistribution_history H with(nolock)
    Inner Join dbo.MSdistribution_agents DA with(nolock)
    On DA.id = H.agent_id
    Inner Join master.sys.servers S with(nolock)
    On S.server_id = DA.subscriber_id
    Where DA.publisher_db = @PublisherDB
    Group By S.name, DA.publisher_database_id)
    , OldestXact (ServerName, OldestEntryTime)
    As (Select MX.ServerName, Min(entry_time)
    From dbo.msrepl_transactions T with(nolock)
    Inner Join MaxXact MX
    On MX.XactSeqNo <= T.xact_seqno
    And MX.PublisherDBID = T.publisher_database_id
    Group By MX.ServerName)
    Select [Replication Status] = Case MD.status
    When 1 Then 'Started'
    When 2 Then 'Succeeded'
    When 3 Then 'In progress'
    When 4 Then 'Idle'
    When 5 Then 'Retrying'
    When 6 Then 'Failed'
    End,
    Subscriber = S.srvname,
    [Subscriber DB] = A.subscriber_db,
    [Publisher DB] = MD.publisher_db,
    Publisher = MD.publisher,
    A.Publication,
    [Current Latency (sec)] = MD.cur_latency,
    [Current Latency (hh:mm:ss)] = Right('00' + Cast(MD.cur_latency/3600 As varchar), 2) +
    ':' + Right('00' + Cast((MD.cur_latency%3600)/60 As varchar), 2) +
    ':' + Right('00' + Cast(MD.cur_latency%60 As varchar), 2),
    [Latency Threshold (min)] = Cast(T.value As Int),
    [Agent Last Stopped (sec)] = DateDiff(hour, agentstoptime, getdate()) – 1,
    [Agent Last Sync] = MD.last_distsync,
    [Last Entry TimeStamp] = OX.OldestEntryTime
    From dbo.MSreplication_monitordata MD with(nolock)
    Inner Join dbo.MSdistribution_agents A with(nolock) On A.id = MD.agent_id
    Inner Join dbo.MSpublicationthresholds T with(nolock) On T.publication_id = MD.publication_id And T.metric_id = 2 — Latency
    Inner Join master.dbo.sysservers S ON S.srvid = A.subscriber_id
    Left Join OldestXact OX On OX.ServerName = S.srvname
    Where MD.publisher = @Publisher
    And MD.publisher_db = @PublisherDB
    And MD.publication_type = 0 — 0 = Transactional publication
    And MD.agent_type = 3; — 3 = distribution agent

    1. Thanks for sharing your script. I’m sure there are many other ways this script could go wrong.

  10. SQLThinker thank you for the post. It works like a charm.
    I suggest add this piece in your select statement so you have the publication names too.
    Publication=MD.publication

Leave a Reply